Hi, I’m trying to figure out how to automate adding conditional formatting to a big range of cells. The cells are not consecutive, but rather 7 rows apart. The conditional formatting should look to a specific cell, and highlight any cell in the specific range that does NOT equal the specific cell.
For example, cell O14 is the cell that all the other cells in row O should look to. Starting with O17, then O24, O31, etc….all the way through O2117 – I need conditional formatting in those specific cells to highlight if the value within that cell <> O14.
The same thing applies to column P (P14 is the reference cell for P17, P24….P2117), and every column all the way through column AL (AL14 is the reference for AL17, AL24…AL2117).
In case additional background helps: All the values in Columns O-AL / Row 14 refer to a Facility Fee. The cells below each reference cell in row 14 are a pricing grid, and each set of prices has a facility fee. I want to call out any values that may not be equal to those in row 14.
I recorded a macro adding the formatting for the first few cells in column O, but I don’t know how to apply it to all of the ranges I need:
Range(“O17,O24”).Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _ Formula1:="=$O$14" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Font .ThemeColor = xlThemeColorAccent2 .TintAndShade = -0.499984740745262 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.599963377788629 End With Selection.FormatConditions(1).StopIfTrue = False
Can anyone help? Let me know if you need more information. Thanks!