Microsoft Office Forums - View Single Post - VBA to highlight specific values in the spreadsheet

p45cal · 05-23-2018, 10:30 AM

Quote:

Originally Posted by daiwuliz

What would need to change in the formula if the values were text, not numbers?

(We seem to have lost that question)
Based on Text and Numbers in column B:

Code:

Sub blah2()
For Each are In Columns(2).SpecialCells(xlCellTypeConstants, 3).Areas
  Row1Addr = are.Cells(1).Offset(, 1).Resize(, 10).Address(False, True)
  With are.Cells(1).Offset(, 1).Resize(4, 10).FormatConditions
    .Delete
    With .Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=" & are.Cells(1).Offset(-1, -1).Address)
      With .Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
      End With
    End With
  End With

  With are.FormatConditions
    .Delete
    With .Add(Type:=xlExpression, Formula1:="=NOT(ISERROR(MATCH(" & are.Cells(1).Offset(-1, -1).Address & "," & Row1Addr & ",0)))")
      With .Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
      End With
    End With
  End With
Next are
End Sub