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

p45cal · 05-22-2018, 06:13 PM

Quote:

Originally Posted by daiwuliz

Can it be done using VBA?

Try this code when the sheet in question is the active sheet:

Code:

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

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

It relies on the positional relationship between the cells containing numbers in column A; it puts conditional formatting (a) in a range 4 rows by 10 columns whose top left corner is one cell below and 2 cells to the right of each cell with a number in column A, and (b) a range 4 rows by 1 columns, offset 1 cell down and 1 cell to the right of that same cell in column A.