Conditional Formatting

Carl Clements

Board Regular
Joined
Jun 13, 2008
Messages
95
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Woffy

New Member
Joined
Oct 21, 2011
Messages
9
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

Board Regular
Joined
Jun 13, 2008
Messages
95
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

New Member
Joined
Apr 15, 2005
Messages
45
Hi,
Can you give an example of your worksheet & I'll see if I can help

Alley
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top