View Full Version : Using And / Or with Conditional Formatting

03-10-2016, 09:08 AM
I've looked at this until my screen is blurry and I can't seem to come up with the correct syntax. What I am attempting to do is test two cells (in VBA) using the AND function and apply conditional formatting accordingly. Specifically, I want to do the following:


I need help getting all of the quotation marks correct to make the above work when applied using VBA.


03-10-2016, 06:01 PM
Well, I don't know what I did, but I kept tweaking my formula and eventually got it to apply the conditional formatting. However, when I went back to my worksheet and attempted to use Format Painter to drag the new formatting to another set of rows (just testing) I noticed that it was highlighting even those rows that didn't meet the defined conditions. When I checked the rules manager for the Conditional Formatting, I noticed that when the conditional formatting is applied, it is applying the formatting to the defined range using absolute cell references. Hence, when I use Format Painter, the new rows were all highlighted as the original row still met both conditions.

Now my problem is how to make the following code translate to a absolute Column and a relative Row reference. Here is the current VBA code:

With Range("A" & Target.Row).Resize(, 11)
.FormatConditions.Add xlExpression, Formula1:="=And($D" & Target.Row & "<>""R"",$J" & Target.Row & "<>""R"")"
.FormatConditions(1).Interior.ColorIndex = 3 'Red
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 2 'White
End With

The end result within the rules manager reflects that the formatting applies to:


How to I change my VBA code to make the formatting apply to:



03-11-2016, 12:08 AM
macro recorder is your friend.

turn it on, select the cells, do your stuff, turn it off and post the recorded macro here.

03-11-2016, 07:16 AM
I tried the macro recorder but couldn't get Excel 2007 to generate any code merely by editing the conditional formatting rule. Also, when I manually edit the rule to change the "Refers To" range from absolute to relative reference as it pertains to the rows only and then clicked "apply," Excel automatically changes the range back to absolute reference.

03-11-2016, 07:45 AM
@Opv My guess is that there is a curly bracket ” in your formula. It should be a plain bracket.

03-11-2016, 08:08 AM
Hmmmm. I just went in again to try and edit the conditional formatting rule. I noticed that when I click on "edit" the actual formula reflects the relative cell reference just the way I want it. However, when I get out of edit mode, the "Refers to" range automatically reflects the absolute reference. I repeated this several times with the same results. How can it be a relative reference in the edit panel and an absolute reference in the rule list view?


I just noticed that I was not comparing apples and oranges in the above post. When I click "Edit" to edit the conditional formatting rule, I am actually editing the formula that tests the applicable cells to see of the designated conditions are met. When I close the Edit panel, that particular formula is displayed on the left side of the entry in the rules list. It is actually the "Applies To" that is the problem. I erroneously thought I was editing that field but that is not the case. As best I can tell (unless I'm missing something) there does not appear to be a way to force the Applies To formula to be a relative reference. Am I correct in that conclusion?

Thanks, @mancubus and @Benzadeus.