Conditional Formatting problem using VBA

[musoguy]

I am trying to write a piece of code that will apply conditional formatting to the ActiveCell:

The ActiveCell will always be in Column B, but the row number will change each time the macro is run.

The cell the conditional formatting refers to will always be in Column G.

However the row the cell is in should be the same row as the ActiveCell.

I can't work out how to write a code that will make the row number of the formula the same as the ActiveCell.

Below is what I have , but it doesn't work! Any help would be greatly appreciated.

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=Offset(0, 5) = 0"
Selection.FormatConditions(1).Interior.ColorIndex = 27

 

[Fazza]

Hi,

Not sure exactly about only wanting to apply to active cell. Can be to any range, BTW.

Suggest you use R1C1 style in conditional format formulas in VBA.

So, could be

   With ActiveCell
      .FormatConditions.Delete
      .FormatConditions.Add Type:=xlExpression, Formula1:="=RC7=0"
      .FormatConditions(1).Interior.ColorIndex = 27
   End With

To always refer to column G = column 7.

Or, to refer to the column 5 away, from B to G, it could be

   With ActiveCell
      .FormatConditions.Delete
      .FormatConditions.Add Type:=xlExpression, Formula1:="=RC[5]=0"
      .FormatConditions(1).Interior.ColorIndex = 27
   End With

HTH, Fazza

 

[musoguy]

Thank you Fazza that solved my problem

 

[Fazza]

Great news, thanks. Keep Excelling, F