Conditional Formatting

[OillyBob55]

Hi all

i have the following code to condition format a cell if its greater than another cell + the figure in another cell (make sense so far) i want to condition format the same cell if its less than a figure too can i tidy up the code i have and add it to it?

The formular for the less than will be

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[6StringJazzer]

greater than another cell + the figure in another cell

That is not what your formula says. It says "greater than another cell times the figure in another cell." Can you clarify what you are doing?

[6StringJazzer]

Do you want to format the cell if it is

greater than $L$31*$Q$31 AND less than $L$31*$R$31

or

greater than $L$31*$Q$31 OR less than $L$31*$R$31

?

[OillyBob55]

Hi Jeff

Sorry i want to greater than $L$31*$Q$31 OR less than $L$31*$R$31 i have a band in the middle that i am aim for so its OR wont be AND.

Thanks

[6StringJazzer]

Replace your first line of code with:

Please Login or Register  to view this content.

I have not tested this but would be happy to do so if you provide your file.

[OillyBob55]

Thanks Jeff

I have changed the code and added the cell i want the formula to work in "M31" i am getting conditional formatting in that cell but it dose not appear to be highlighting it.

Please Login or Register  to view this content.

[OillyBob55]

I have knocked together a quick book to test it

[6StringJazzer]

You have to use the code exactly as is. Do not change A1 to M31. Range("A1") means "the first cell in the range if they have selected more than one cell."

Also I included too many quotes.

Here is the corrected version.

I am thinking you really want AND but I didn't change it. Every number will always be greater than the first number or less than the second, if the second is greater than the first.

[OillyBob55]

Thanks Jeff that works but it doesn't work with my set up. That will condition format cell "M31" i put some code in to always select "M31" i have some other code that drops data into that table and moves the list down by one. Th conditional formatting stays with the cells as they move down but the cell its looking at is away "M31" the formula increases by one each move but not the cell its looking at.

=OR(M31<$L$32*$R$32,M31>$L$32*$Q$32)

So the cells in red need to match the number ones in the equation

Not sure that makes any sense

[6StringJazzer]

You might want to read up on how conditional formatting works. When you have one CF formula that applies to multiple cells, the cell reference is always the same.

Maybe this will help.