Forum Discussion
Solved
Using conditional formatting to change color of cells depending on its value with text in a cell
I am trying to change the color of a cell according to its value but there are text combined with it which makes it difficult. For example, instead of changing the cell to yellow when its in between 3.5 to 5 , I want to change the color when its 3.5ms to 5ms. There are strings combined with the numbers. I tried using
=I6 >= 6 & "ms" to change the cell to red which doesnt really work as values that are 6.01ms to 6.99ms and values above 10ms wont change even though it fits the criteria. I believe the "ms" screwed it up but I really cant not have the "ms" in it.
Is there a way to compare values with text combined with it? Would really appreciate your help. Thanks!
=I6 >= 6 & "ms" to change the cell to red which doesnt really work as values that are 6.01ms to 6.99ms and values above 10ms wont change even though it fits the criteria. I believe the "ms" screwed it up but I really cant not have the "ms" in it.
Is there a way to compare values with text combined with it? Would really appreciate your help. Thanks!
Jelaamii The " ms" part is just a format. It's not part of the cell value. Have a look at the attached file, to see what I mean.
6 Replies
If that's always "ms" at the end you may use something like
=REPLACE(I6,LEN(C16)-1,2,"")*1 >= 6- SergeiBaklan thanks for your reply. Do you mind breaking down how does this formula work? I have tried using it but it only works if i did not input the value with "ms". For example, it stays the same color when i input "6ms" into the cell but it does change to red when i input only "6"
If you enter just numbers better to use Riny_van_Eekelen suggestion
Jelaamii Play around with custom number formats as demonstrated in the picture below. You enter a number but Excel displays it with the " ms" at the end. But, it's still a number that you can use in conditional formatting.
- Riny_van_Eekelen thanks for replying. I have tried your suggestion but having this error
Jelaamii The " ms" part is just a format. It's not part of the cell value. Have a look at the attached file, to see what I mean.
