VBA Conditional Formatting Multiple Columns Variable LR

[pantlegger]

I have run into a hard place with this. Here is the objective of the workbook. It is a sales tracker trend report for all 12 months. Because employees come and go, I use the last imported sheet as the current "roster". Because this is updated by different people, I am trying to make all one macro. The last piece of the puzzle is to get certain columns to conditional format, however, it won't do all the columns. It is only doing the column that corresponds to the last imported sheet.

I am still a beginner with VBA so I don't know where to begin to troubleshoot as theirs no error code and I can't find any help online. Thanks

Here is the code I have so far:

"Dim rg As Range Dim rg1 As Range
Dim rg2 As Range
Dim rg3 As Range
Dim rg4 As Range
Dim rg5 As Range
Dim rg6 As Range
Dim rg7 As Range
Dim rg8 As Range
Dim rg9 As Range
Dim rg10 As Range
Dim rg11 As Range
Dim cond1 As FormatCondition
Set rg = Range("E6", Range("E6").End(xlDown))
Set rg1 = Range("G6", Range("G6").End(xlDown))
Set rg2 = Range("I6", Range("I6").End(xlDown))
Set rg3 = Range("K6", Range("K6").End(xlDown))
Set rg4 = Range("M6", Range("M6").End(xlDown))
Set rg5 = Range("o6", Range("o6").End(xlDown))
Set rg6 = Range("Q6", Range("Q6").End(xlDown))
Set rg7 = Range("S6", Range("S6").End(xlDown))
Set rg8 = Range("U6", Range("U6").End(xlDown))
Set rg9 = Range("W6", Range("W6").End(xlDown))
Set rg10 = Range("Y6", Range("Y6").End(xlDown))
Set rg11 = Range("AA6", Range("AA6").End(xlDown))

rg.FormatConditions.Delete
rg1.FormatConditions.Delete
rg2.FormatConditions.Delete
rg3.FormatConditions.Delete
rg4.FormatConditions.Delete
rg5.FormatConditions.Delete
rg6.FormatConditions.Delete
rg7.FormatConditions.Delete
rg8.FormatConditions.Delete
rg9.FormatConditions.Delete
rg10.FormatConditions.Delete
rg11.FormatConditions.Delete

Set cond1 = rg.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg1.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg2.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg3.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg4.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg5.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg6.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg7.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg8.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg9.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg10.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond1 = rg11.FormatConditions.Add(xlCellValue, xlLess, "=100%")


With cond1
.Interior.Color = vbRed
End With"

 

[pantlegger]

Solved my own problem. Thought I share


"Dim rg As Range Dim rg1 As Range
Dim rg2 As Range
Dim rg3 As Range
Dim rg4 As Range
Dim rg5 As Range
Dim rg6 As Range
Dim rg7 As Range
Dim rg8 As Range
Dim rg9 As Range
Dim rg10 As Range
Dim rg11 As Range
Dim cond1 As FormatCondition
Dim cond2 As FormatCondition
Dim cond3 As FormatCondition
Dim cond4 As FormatCondition
Dim cond5 As FormatCondition
Dim cond6 As FormatCondition
Dim cond7 As FormatCondition
Dim cond8 As FormatCondition
Dim cond9 As FormatCondition
Dim cond10 As FormatCondition
Dim cond11 As FormatCondition
Dim cond12 As FormatCondition
Set rg = Range("E6", Range("E6").End(xlDown))
Set rg1 = Range("G6", Range("G6").End(xlDown))
Set rg2 = Range("I6", Range("I6").End(xlDown))
Set rg3 = Range("K6", Range("K6").End(xlDown))
Set rg4 = Range("M6", Range("M6").End(xlDown))
Set rg5 = Range("o6", Range("o6").End(xlDown))
Set rg6 = Range("Q6", Range("Q6").End(xlDown))
Set rg7 = Range("S6", Range("S6").End(xlDown))
Set rg8 = Range("U6", Range("U6").End(xlDown))
Set rg9 = Range("W6", Range("W6").End(xlDown))
Set rg10 = Range("Y6", Range("Y6").End(xlDown))
Set rg11 = Range("AA6", Range("AA6").End(xlDown))

rg.FormatConditions.Delete
rg1.FormatConditions.Delete
rg2.FormatConditions.Delete
rg3.FormatConditions.Delete
rg4.FormatConditions.Delete
rg5.FormatConditions.Delete
rg6.FormatConditions.Delete
rg7.FormatConditions.Delete
rg8.FormatConditions.Delete
rg9.FormatConditions.Delete
rg10.FormatConditions.Delete
rg11.FormatConditions.Delete

Set cond1 = rg.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond2 = rg1.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond3 = rg2.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond4 = rg3.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond5 = rg4.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond6 = rg5.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond7 = rg6.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond8 = rg7.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond9 = rg8.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond10 = rg9.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond11 = rg10.FormatConditions.Add(xlCellValue, xlLess, "=100%")
Set cond12 = rg11.FormatConditions.Add(xlCellValue, xlLess, "=100%")


With cond1
.Interior.Color = vbRed
End With
With cond2
.Interior.Color = vbRed
End With
With cond3
.Interior.Color = vbRed
End With
With cond4
.Interior.Color = vbRed
End With
With cond5
.Interior.Color = vbRed
End With
With cond6
.Interior.Color = vbRed
End With
With cond7
.Interior.Color = vbRed
End With
With cond8
.Interior.Color = vbRed
End With
With cond9
.Interior.Color = vbRed
End With
With cond10
.Interior.Color = vbRed
End With
With cond11
.Interior.Color = vbRed
End With
With cond12
.Interior.Color = vbRed
End With"