Conditional Formatting or Macro?

[jaronson]

I have a spreadsheet set up so that a certain cell is conditionally formatted to change its fill color once the word "paid" has been entered. The cell is in column E and repeats every 10 rows or so down the page with other information in between.

I want the formatting change to also apply to the 3 cells to the left, but only when the word "paid" is entered in column E. For example, if I enter "paid" in E5, I want B5:E5 to change fill color accordingly.

Can this be done, and what's the best way? Conditional Formatting? Macro? Other?

I'm relatively new to VBA language, so if that is the appropriate route, please take my novice into consideration.

Thanks!

 

[VoG]

No need for code - see this example http://www.contextures.com/xlCondFormat02.html

 

[jaronson]

Thanks for the quick response. That was helpful and did what I asked for, but now I realize I should have been more descriptive about what I'm doing.

In the end, I would like this action in the form of a macro. The reason being: I eventually want a macro that inserts a group of preformatted cells, and this "conditional formatting feature" is a part of the whole.

For now, I am simply trying to create a macro that will apply the aforementioned "conditional formatting" to the active cell.

I tried to record a macro and then alter it, but I'm having trouble.

The first was recorded without the "relative cell" button. It works, but only for the specified cells, no matter what the active cell is when run.

Range("B11:F11").Select
Range("F11").Activate
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$F11=""paid"""
Selection.FormatConditions(1).Interior.ColorIndex = 36


The second was recorded with the "relative cell" button. It doesn't work at all.

ActiveCell.Offset(-3, -5).Range("A1:E1").Select
ActiveCell.Offset(-3, -1).Range("A1").Activate
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$F20=""paid"""
Selection.FormatConditions(1).Interior.ColorIndex = 36

Any advice is much appreciated.

 

[VoG]

You should probably try to avoid using ActiveCell but try

Sub test()
With ActiveCell.Resize(, 5)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & ActiveCell.Address(False, True) & "=""paid"""
    .FormatConditions(1).Interior.ColorIndex = 36
End With
End Sub

 

[jaronson]

Thanks. That's definitely an improvement.

The only problem, and this may be an easy fix, sorry: it formats the 4 cells to the right, not to the left. I tried changing to

ActiveCell.Resize(, -5)

but apparently excel doesn't like that.

 

[VoG]

Try

With ActiveCell.Offset(, -4).Resize(, 5)

 

[jaronson]

works perfectly! thanks so much.