Highlighting Values using conditional formatting

Topic

New Reply

I have data in Col K & L. Where the values in Col K < than the value in Col L, then the cell/s in Col K that is less than the value in Col L in the same row as Col K must be highlighted in Blue

I have tried to do this using conditional formatting but cannot get it to work. I have set up a separate sheet (desired result) to show you what it should look like if conditional formatting was applied

Your assistance in resolving this will be most appreciated

Reply | Quote

Replies

Assuming I am following you right look at the attached file and try changing a few values, is this what you want to do?
If it is then a very simple way to do it is to highlight the cell that you want to change the background of if the value is lower.
Click on Conditional formatting then on Highlight Cell Rules.
From here you have a number of options, choose Less Than.
In the box that opens type in your reference cell, for example =L5 and change the condition in the right hand box to whatever colour you want it to be.
Do this for each of the cell references you want to highlight.

Reply | Quote

Hi Alan

Thanks for input & help, much appreciated

Howard

Reply | Quote

You’re trying to use a < comparison with cells that contain text and that doesn't compute.
Put the text in separate columns and then use the < comparison.

Reply | Quote

Hi Kweaver

Thanks for the advise

Howard

Reply | Quote

Partial answer. The problem is maybe that your values are text but you want a numeric comparison “11.37” is alphabetically less than “7.60” but numerically greater. I tried the following:

Enter the data as numbers and apply a number format ‘0.00 “Times”‘ (double quotes are part of the format) so the display is similar to your example. Then apply your conditional format which should work as expected. (11.37 is not less than 7.60 and is not highlighted)

Partial answer because you might not be able to separate the numbers from the text, and I don’t know why some are “times” and some are “days” and if it matters

Ian

Reply | Quote

Hi Ian

Thanks for the advise. I now realize where the problem lay. Your solution worked perfectly

Howard

Reply | Quote

You are comparing text to text and a “1” < "7" so you get the expected results. You can convert all the cells to numbers, or use something like this for your conditional format:

Code:

=VALUE(LEFT(K5,FIND(" ",K5)-1))<=VALUE(LEFT(L5,FIND(" ",L5)-1))

This compares the number to the other number by extracting the text before the space and converting it to a value.

Steve

Reply | Quote

Hi Steve

Thanks for the advise and input. I tried your solution and it worked spot on

Howard

Reply | Quote

Hi Guys

Thanks for all your input much appreciated.

Howard

Reply | Quote