Conditional Formatting Problem

[cosmica67]

HI,

I have attached a test excel file where I have used conditional formatting to colour cells according to a percentage score. I have also pasted an image of the conditional formatting rules for information.

The problem I have is that I want the blank cells to be formatted the same colour as the range I have asked conditional formatting to colour but at the moment they are clear (white). I need the blank cells be included in column A as there is more data in columns B-D which I have not included in the test file.

If I use conditional formatting to colour using 'Blanks' I can only get one colour and I need blanks to be coloured in 4 colours (Yellow, blue, purple and green).

Other than colouring the blanks in manually, is there anything I can do or add to conditional formatting to do this automatically?

Many thanks,

[WideBoyDixon]

Changing the CF to use formulas may work. For example, I changed the purple rule to:

Please Login or Register  to view this content.

Which looks for the next non-blank value from the current cell to retrieve the value. I also set the "Stop If True" condition. See attached on a copy of your sheet.

WBD

[cosmica67]

Hi, WideBoyDixon.

Many thanks for looking at this - I've had a look and it almost works but when there is a gap like the one I have pasted, the colours are not quite right:

Attachment 664684

In the attachment, A55 and A56 should be coloured green as well.

Is there a way around this?

Many thanks.

[WideBoyDixon]

OK. You can change the formula to:

Please Login or Register  to view this content.

etc. for the others. See attached.

WBD

[cosmica67]

Hi, WideBoyDixon.

That LOOKUP formula works a treat!

Many thanks for your valued expertise!

Regards