SOLVED

New Contributor

# 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!
6 Replies

# Re: Using conditional formatting to change color of cells depending on its value with text in a cell

@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.

# Re: Using conditional formatting to change color of cells depending on its value with text in a cell

If that's always "ms" at the end you may use something like

``=REPLACE(I6,LEN(C16)-1,2,"")*1 >= 6``

# Re: Using conditional formatting to change color of cells depending on its value with text in a cell

@Riny_van_Eekelen thanks for replying. I have tried your suggestion but having this error

# Re: Using conditional formatting to change color of cells depending on its value with text in a cell

@Sergei Baklan 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"
best response confirmed by Jelaamii (New Contributor)
Solution

# Re: Using conditional formatting to change color of cells depending on its value with text in a cell

@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.

# Re: Using conditional formatting to change color of cells depending on its value with text in a cell

If you enter just numbers better to use @Riny_van_Eekelen suggestion