Conditionally Format Multiple Columns

[honkin]

I am trying to remove the standard Conditional Formatting I initially added from a sheet as I found that over time, there ended up being so many rules listed and the sheet became seriously slow. After deleting all the CF, it is now behaving as it should, but I still need to colour those columns.

So I am trying to write a VBA macro to handle the individual columns, but have a section which remain red, telling me the syntax is not correct.

Here is the code to date

Sub ConditionalFormattingPowerSheets()
Dim MyRange As Range
'Create range object
    Set MyRange = Union(.Range(“AQ21:AS" & LastRow),.Range(“BH21:BH" &LastRow),.Range, _
    (“BK21:BK" & LastRow),.Range(“BN21:BN" & LastRow),.Range(“BQ21:BQ” & LastRow),.Range, _
    (“BT21:BT" & LastRow))
'Delete previous conditional formats
MyRange.FormatConditions.Delete
'Add first rule
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, _
        Formula1:=“=0"
MyRange.FormatConditions(1).Interior.Color = RGB(167, 255, 167)
'Add second rule
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=0"
MyRange.FormatConditions(2).Interior.Color = RGB(251, 178, 178)

End Sub

As you can see, there is a group of 3 columns together (AQ to AS) then 5 individual columns (BH, BK, BN, BQ & BT).

The entire section for Set MyRange remains red. That seems to be the only area in question.

Any thoughts on what I am doing wrong in that section?

cheers

 

[jasonb75]

You can't split a parameter over 2 lines.
The underscore / new line needs to be after the close bracket comma, not in front of the open bracket.

 

[rollis13]

You have two commas at the end of the rows in that line that shouldn't be there and other missing sheet reference. Try:

Option Explicit
Sub ConditionalFormattingPowerSheets()
    Dim MyRange As Range
    Dim lastrow As Long
    With ActiveSheet
        'Create range object
        lastrow = .Range("AQ" & .Rows.Count).End(xlUp).Row
        Set MyRange = Union(.Range("AQ21:AS" & lastrow), .Range("BH21:BH" & lastrow), _
            .Range("BK21:BK" & lastrow), .Range("BN21:BN" & lastrow), _
            .Range("BQ21:BQ" & lastrow), .Range("BT21:BT" & lastrow))
    End With
    'Delete previous conditional formats
    MyRange.FormatConditions.Delete
    'Add first rule
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=0"
    MyRange.FormatConditions(1).Interior.Color = RGB(167, 255, 167)
    'Add second rule
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"
    MyRange.FormatConditions(2).Interior.Color = RGB(251, 178, 178)
End Sub

 

[honkin]

You can't split a parameter over 2 lines.
The underscore / new line needs to be after the close bracket comma, not in front of the open bracket.

ah understood. Thanks for that

 

[honkin]

You have two commas at the end of the rows in that line that shouldn't be there and other missing sheet reference. Try:

Option Explicit
Sub ConditionalFormattingPowerSheets()
    Dim MyRange As Range
    Dim lastrow As Long
    With ActiveSheet
        'Create range object
        lastrow = .Range("AQ" & .Rows.Count).End(xlUp).Row
        Set MyRange = Union(.Range("AQ21:AS" & lastrow), .Range("BH21:BH" & lastrow), _
            .Range("BK21:BK" & lastrow), .Range("BN21:BN" & lastrow), _
            .Range("BQ21:BQ" & lastrow), .Range("BT21:BT" & lastrow))
    End With
    'Delete previous conditional formats
    MyRange.FormatConditions.Delete
    'Add first rule
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=0"
    MyRange.FormatConditions(1).Interior.Color = RGB(167, 255, 167)
    'Add second rule
    MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"
    MyRange.FormatConditions(2).Interior.Color = RGB(251, 178, 178)
End Sub

cheers rollis13. That nailed it. Just ran it and it coloured those columns perfectly.

Thanks so much for the lesson. I was part the way there, but my grasp of when to split a line and a couple of other things wasn't up to scratch

 

[rollis13]

Thanks for the positive feedback, glad we were of some help.