VBA Conditional Formatting - 4-7 conditions

[Doodles1]

Hi there,

I'm new to VBA and to be honest it scares me!

Basically I have a number of columns that need conditional formatting with more than the easy peasy three conditions.

Example 1

If CellValue >20 Cell needs to be filled Red (Colour Index 3)

If CellValue <-20 Cell needs to be filled Red (Colour Index 3)

If CellValue >10 Cell needs to be filled Light Orange (Colour Index 45)

If CellValue <-10 Cell needs to be filled Light Orange (Colour Index 45)

If CellValue <10 Cell needs to be filled Lime (Colour Index 43)

If CellValue >-10 Cell needs to be filled Lime (Colour Index 43)

If Cell is blank then there needs to be no fill.


Example 2

If CellValue >0.2 Cell needs to be filled Red (Colour Index 3)

If CellValue >0.1 Cell needs to be filled Light Orange (Colour Index 45)

If CellValue <0.1 Cell needs to be filled Lime (Colour Index 43)

If Cell is blank then there needs to be no fill.

The values in the cells (range = H2:H101 for Example 1 and P2:P101 for Example 2 are calculated by a formula or brought back using a VLOOKUP and are liable to change when data in the spreadsheet is refreshed.

I was using the nice and simple conditional formatting for Example 2 but I found that blank cells were being formatted to a Red fill!

Can anybody help me with some coding and save me from tearing any more of my hair out?!

Thank you!!!

 

[shg]

Welcome to the board.

You can do this with three conditions. One way:

1. Cell value is equal to ="" format with no fill
2. Formula Is =ABS(A1)>20 format red
3, Formula is =ABS($E$3)>10 format orange

Then format the cell as green.

 

[Doodles1]

Hi,

Conditions 2 and 3 work well so thank you very much for that.

However the first condition doesn't appear to work and now blank cells are green rather than red.

Any other suggestions?

If anyone has some coding that I can amend to suit my conditions I'd be really interested to see it :0)

 

[Jonmo1]

Try

Condition1 : =ABS(A1)>20 - Format Red
Condition2 : =ABS(A1)>10 - Format Orange
Condition3 : =ABS(A1)>0 - Format Green