Conditional Formatting only formats first cell in selection

[kayak12]

Hello!

My conditional formatting only works in the first cell of the series, or the entire series changes based on one cell change. I cannot figure out what is wrong! Below I describe how I set up the CF. Please help!

I use Excel 2010.

I want an empty cell in Row E to shade red if, from the drop down, someone selects "Not on aspirin: has a contraindication - described in comments below" (from column D).

1. I highlighted all the cells in Row E that I want changed.
2. I opened CF and selected "Use a formula to determine which cells to format"
3. In the box I entered: $D$9="Not on aspirin: has a contraindication - described in comments column"
4. I selected the format I wanted (red box)
5. I clicked OK to get back to my screen
6. I tested the first cell: In D9 I selected from the drop down "Not on aspirin...."and all of the E cells I selected for CF turned Red.


Then, I tried another way:
1. I selected only the first cell in Row E -- $E$9
2. I opened CF and selected "Use a formula to determine which cells to format"
3. In the box I entered: $D$9="Not on aspirin: has a contraindication - described in comments column"
4. I selected the format I wanted (red box)
5. I clicked OK to get back to my screen
6. I tested the first cell: In D9 I selected from the drop down "Not on aspirin...." and it worked!
7.Then I tested the next cell, D10 and the CF did not work.

I tried a few other things based on other forums:
- removing the quotations - received an error message
- highlighted the D cells instead of the E cells, nothing changed
- I removed the " : " and " - " from the drop down and in the formula when I removed the quotations - received the same error message

I can't figure this out Please help!

 

[Obied70]

I find the same thing happens when I pose a question and go back to the issue; sometimes the break helps me look at it a different way. Glad to hear you got it sorted out.

 

[Roger B]

I too am being driven mad by this.

I tried removing the offending $ sign, but it gets put back automatically.

 

[RoryA]

Welcome to the forum.

What formula are you trying to use, and what exactly is happening?

 

[Roger B]

Thanks for the welcome

Basically I have rows of data, one of the columns (AB) is a flag with possible values of 1,2 or 3 and I'm trying to highlight the row according to the flag value. I can do it using VBA, but I reckoned a formula would be more elegant.

I'm setting the rule at "This Worksheet" level
Select "Use a formula to determine which cells to format"
Set "Format values where ..." to AB17 >0 (just testing on one row - I'll need to replicate the formula over more rows)
Click the "Format" button
Select a fill colour - press "OK"
Press "OK" again to edit the "Applies to" field, which is pre-loaded with =$P$17 (I think that was the active cell before I went into Conditional Formatting)
Put the cursor behind the 7 and delete $P$17, leaving the = sign
Type in A17:T17 and click "OK"
Test it and nothing happens
Go back into "Conditional Rules Formatting Manager"
Formula has changed to ="AB17>0" (note the quotes) and the "Applies to" field is now =$A$17:$T$17
I put the cursor behind the last 7 in the range field and click left arrow and $A$17 is automatically appended to the data in the field
I cancel and return to edit the formula to remove the quotes from the formula to give =AB17>0 (it still tries to add in $A$17, but with a bit of messing I get rid of that)
It now works on the 1st column only.

Reading previous posts I'm sure the problem is connected with the errant $ signs in the range, but I can't get rid of them!

Help much appreciated!

 

[RoryA]

Your formula should be:
=$AB17 >0

The 'applies to' range should have $ on it - it doesn't change. To apply to multiple rows, you only need to change it to:
=$A$17:$T$200
for example. The row number in the formula is not absolute so it will adjust for each row in the applies to range (the column won't)

 

[Roger B]

Thanks Rory, that fixed it for one row, I'll see if I can expand it to cover every row.

It seems odd that the address of the cell being queried affects which columns get highlighted - intuitively it should be the "applies to" range that defines which columns get highlighted.

 

[RoryA]

I disagree. The applies to range is fixed when you apply the formatting. It is the formula that needs to adjust depending on the context of the cell where the formatting occurs.

 

[Roger B]

I got three rules in place over 3000 rows, unfortunately it slowed the whole thing down so I've gone back to my VBA subroutine.

Still, something else learned!

Thanks for all the help

 

[yannmn]

I have a similar issue. I'm using N3 value to highlight F3:F23 but only F3 is highlighting.

Here is the format rule =IF($N3>50%, $N3<100%) which works but just on F3
and it applies to =$F$3:$F$23

thanks for your help

 

[saraqual]

Hi there,

I am trying to use CF to highlight cells in one column depending in the values in the adjacent column
what I mean is if A1<B1 highlight A1
if A2> B2 do not highlight and so on...
I am selecting A Column ==> CF ==> Use a formula to determine which cell to format and I typed =(A1<B2) but it sometimes highlights cells that are higher than adjacent in B column..
However, it seems from what I read that I need to do it row by row which is impossible cuz I have like a 1000 rows..
I would highly appreciate if anyone has an idea about how to do this