Hi All, I have a spreadsheet that I have for a contract since the beginning of time so there are a lot of rows where the header is "May-13", "June-13" etc... each month I add a column to the end for the current month. I have code that will conditionally format one row but I only want to conditionally format the most current 12 months which are the only ones visible. All other months are hidden. I tried it and it is selecting every single row including the hidden ones. Below is the code that I used. Since there is a variable number of columns. I am using the header in my totals column ("12 Month Summary") to get to the right cell by offsetting. Then I want to select just the last 12 cells in the row.

Cells.Select
Selection.Find(What:="12 Mth Summary", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(5, -2).Select
Range(Selection, Selection.End(xlToLeft).Offset(0, 1)).Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueLowestValue
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 16776444
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 8109667
.TintAndShade = 0
End With