ConditionalFormat() for a range

[jvanbonn]

My goal is to have code to set a dynamic range to the variable myRng and then set conditional formatting such that if A(i) =true then row(i) is Grey, Italic, and Strikethrough.

What I notice happening is that the conditional format formula becomes "$A1048535=TRUE"

It acts like there is a application variable that keeps counting up every time I run the script.

[COLOR=Blue]Sub[/COLOR] CreateConditionalFormat(senior [COLOR=Blue]As String[/COLOR])
[COLOR=SeaGreen]'
' CreateStrikeGreyCondition Macro
'[/COLOR]
[COLOR=Blue]Dim[/COLOR] myRng [COLOR=Blue]As[/COLOR] Range

[COLOR=Blue]Set[/COLOR] myRng = Sheets(senior).[B4].CurrentRegion

Cells.FormatConditions.Delete
  [COLOR=Blue]With[/COLOR] myRng.FormatConditions _
    .Add(xlExpression, xlExpression, "=$A5=True")
    [COLOR=Blue]With[/COLOR] .Font
      .Color = RGB(90, 90, 90)
      .Italic = [COLOR=Blue]True[/COLOR]
      .Strikethrough = [COLOR=Blue]True[/COLOR]
    [COLOR=Blue]End With
  End With
End Sub[/COLOR]

 

[Norie]

What range do you want to apply this conditional formatting to?

What's the address of the cell in the top left of the range?

Is it in row 5?

 

[jvanbonn]

The upper left cell is always B4, but row 4 is always header information.
The number of rows varies, hence my use of :

Set myRng = Sheets(senior).[B4].CurrentRegion

 

[Norie]

Well that's going to include the header row and therefore put the formula off, perhaps just by one row but perhaps more.

Excel VBA can be quite picky when setting conditional formatting using formulas.

This will work but it'll also apply the conditional formatting to the header row.

Option Explicit
 
Sub CreateConditionalFormat(senior)
'
' CreateStrikeGreyCondition Macro
'
Dim myRng As Range
 
    Set myRng = Sheets(senior).Range("B4").CurrentRegion
 
    Cells.FormatConditions.Delete

    With myRng.FormatConditions _
         .Add(xlExpression, xlExpression, "=R[0]C1")

        With .Font
            .Color = RGB(90, 90, 90)
            .Italic = True
            .Strikethrough = True
        End With

    End With
 
End Sub

If you don't want to include the header row or any other cells/ranges you'll need to use some other method to get the correct range.

Oh, forgot to mention - I changed the formula to R1C1 notation, VBA seems to 'like' that better for conditional formatting.

 

[jvanbonn]

Thanks Norie!

That fixed my problem.

Also, setting a conditional format for the header row is not a problem because A4 (R4C1) will never be TRUE.

 

[Norie]

No problem.

Though the header row wouldn't really matter, you might want to be careful when using CurrentRegion for other things though.

 

[jvanbonn]

Do you have any suggestions to define a range of cells that always starts in R4C2 with variable number of additional rows and columns.

I've considered using the ".Cells(row.count,i).End(xlUp)" method, but column(i) of the last row may be blank.

Thanks again!