Conditional Formatting in Excel VBA

[1968rm]

I am trying to delete all conditional formatting from a sheet, and add new formatting. I use the following macro (it's recorded) but when I run it, it gives me an error by .ThemeColor = xlThemeColorDark1 towards the end of the macro. The first conditional format highlights the cell yellow, and the second one changes the font to white.

Sub PLConditionalFormatting()
    Cells.FormatConditions.Delete
    Columns("L:L").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=A1=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("F:L").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=B1=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[welshgasman]

Thanks,

I think I might have the cell selection correct, but struggling over writing the formula?

Sub testCell()
Dim rngdata As Range
Dim llastrow As Long
Dim i As Integer


    Set rngdata = Range("A1").CurrentRegion
    i = Application.WorksheetFunction.Match("Commission", Range("A1:AA1"), 0)
    Range("A1").Select
    With Range(Cells(llastrow, i).Address, Cells(llastrow, i).Address).FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=NOT(ISBLANK" & Cells(3, i + 3) & ")")
        .Interior.Color = RGB(161, 212, 144)
     '   '.Font.Color = RGB(0, 97, 0)
    End With
End Sub

I'll try experimenting over the weekend.

 

[welshgasman]

I'd appreciate some help on where I am going wrong?
The code below (just test code for now) works correctly on a column and refers to the correct column for activation, but it highlights the whole selection when the cell is populated and not just the corresponding cell for the row.?

    Range("G1:g7").Select
    Cells.FormatConditions.Delete
    Range("G3:g10").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=NOT(ISBLANK(R3C[2]))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

TIA

 

[Fazza]

as posted above, R3C[2] refers to row 3 and two columns to the right. So for all cells in G3:G10 the check is against row 3 entry
as posted below, RC[2] refers to same row and two columns to the right
if always column "I" then use RC9
with this notation, one can avoid selecting
alternative to NOT(ISBLANK()) is use LEN()

  With Range("G3:g10")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(RC[2])"
    .FormatConditions(1).Interior.ColorIndex = 3
  End With

 

[Peter_SSs]

@welshgasman
What version of Excel are you using?

 

[welshgasman]

Fazza,

Thank you. I now understand. That works great. I will prefer to use the RC notation in this part of the code as columns might need to be inserted later, and that would save me altering the code each time. As long as I keep the headings the same of course. :D

many thanks for the help.

 

[welshgasman]

Peter,

I'm using Excel 2007. old I know but it does the job.

 

[Peter_SSs]

Peter,

I'm using Excel 2007. old I know but it does the job.

OK, thanks. Yes, in 2007 (& earlier) you need to either select an appropriate range using the code before applying the CF formula in A1 style, or use R1C1 style as suggested by Fazza.

FYI, in versions from 2010 on, the CF can be applied directly by the code (without any selection issues) using A1 style if you want.

 

[welshgasman]

Peter,

I'm still getting the hang of where to select for conditional formatting even in the UI. I ended up recently where the cell was being highlighted by the cell on the previous or next row, as I started in the wrong place.

Fazza save me a lot of trial and error for the correct syntax. :D

 

[Peter_SSs]

Peter,

I'm still getting the hang of where to select for conditional formatting even in the UI. I ended up recently where the cell was being highlighted by the cell on the previous or next row, as I started in the wrong place.

You just need to selct the range that you are applying the format to, and ensure that the active cell is in the appropriate row &/or column for the formula you are adding.

For example, you were trying to format G3:Gxx and the CF formula that you had applied to row 3.
So manually you would select from G3 to the end of the column G data then apply that CF formula.

In code, it would be like this.

With Range("G3:G" & lLastRow - 2)
  .Select
  .FormatConditions.Delete
  .FormatConditions.Add Type:=xlExpression, Formula1:="=LEN($I3)"
  .FormatConditions(1).Interior.Color = RGB(161, 212, 144)
End With

I'm not suggesting that you should move away from the R1C1 method or that this is in any way better, just pointing out that you can do the CF with A1 style referencing - and that from 2010 on that 'Select' line is no longer required..

 

[welshgasman]

Thank you Peter,

The RC notation is handy for me at present as I can search for a heading and use the column number found in the formula. When I first started I was using "G3", but then if a column was inserted before G, I was having to modify the code. I like the idea that I can do the above without modifying the code each time. :D The workbook I am creating is being built bit by bit as I find out the VBA way of doing something. The source sheet did have CF on it, but due to referring to a different column (more columns in the source sheet) it was not having any effect, so I have to apply it on each creation of the sheet.

FWIW I do not believe I had to use the select in my original code, but that is in work and I will not be able to check until Tuesday. I *thought* the range was implied by the With statement?

Thanks again.