Conditional Formatting

[Carl Clements]

Hi,

I'm applying some conditional formatting to a column, which highlights cells if a number is >10 or <-10, however, it also formats cells with text (eg headers).

Firstly, does anybody know why it is formatting text, and secondly, how I can avoid it so it looks at numbers only?

Thanks,
Carl

 

[Woffy]

I've had the same problem and have come up with two work arounds, but they're a bit basic and may not suit:

If you only have text in the header, apply the conditional formatting to the whole column then select the header cell and delete the conditions for that cell.

If you only have one text string appearing amongst your numbers (i.e. if they're not a number then they all say the same thing, e.g. "MISSING") then make the first condition IF CELL VALUE IS EQUAL TO MISSING, then just leave the formatting bit as it is, and do your other two conditions as conditions 2 and 3.

If you have multiple text strings in amongst your numbers, then I'm afraid someone with a bit more experience is going to have to help!

 

[Carl Clements]

Thanks. Unfortunately I can't use the first work around because the cells with text are not constant and change on different days (other than the first header).

I tried the second work around but for some reason this only works on the first header, even though the text and format of the headers below the first header are the same as the first. I'm also unable to manually change the format of the cell, without removing the conditional formatting. Quite frustrating!

 

[apearson]

Hi,
Can you give an example of your worksheet & I'll see if I can help

Alley

 

[Carl Clements]

Seems to be working now. I have no idea what has changed but all good. Thanks all.