Inconsistent highlighting with conditional formatting.

[OaklandJim]

First, I tried XL2BB but when I paste I just get a bunch of text. How do I insert what XL2BB copies? Below is a picture instead.

I must be doing something wrong! I am getting inconsistent highlighting with conditional formatting.

In column labeled Confirmed there are boolean values. Cells include TRUE if a prospect has been confirmed. I want to highlight rows where the prospect is not confirmed.

It works on most lines but not all. Note row 9 which has a value of FALSE value in the confirmed column but the conditional formatting does not highlight that row.

In the rule for cells involved, I have this formula

=NOT(rrConfirmed)

. rr indicates relative row addressing for the name rrConfirmed.

So in any given row the name rrConfirmed refers to the cell in the column labeled Confirmed in the respective row. I did double check to make sure that the name rrConfirmed points to the correct cell in each row.

FWIW the formula in the Confirmed column cells is

=IFERROR(IF(rrLastName="", "", IF(FIND("confirmed", rrStatus)>0,TRUE,FALSE)),FALSE)

What might I look at to try to fix this? I am almost ready to just write code that highlights and unhighlights rows but that is less good than conditional formatting which highlights/unhighights rows for unconfirmed prospects quickly. Unfortunately doing it with code would seem to require use of the SelectionChange or Change event which can be a bit herky-jerky?

Thanks for your help. This has me a bit down.

Notice that

 

[OaklandJim]

I reversed the order of my rules for conditional formatting. The highlighting for an unconfirmed person is first condition now and the alternating row shading is the second condition. That works.

 

[Peter_SSs]

I tried XL2BB but when I paste I just get a bunch of text.

That is what should happen. Once you have that 'bunch of text' from XL2BB, try pressing the 'Preview' button at the top right. That will show you what will actually appear once you click 'Post reply'