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!

 

[RoryA]

Welcome to the forum.

If you use relative row references (i/e/ no $ in front of the row number) it will adjust for each row. So applying a formula like =A1>B1 to the Range A1:A1000 will automatically adjust to test row 2 for A2, row 3 for A3 and so on.

 

[saraqual]

Welcome to the forum.

If you use relative row references (i/e/ no $ in front of the row number) it will adjust for each row. So applying a formula like =A1>B1 to the Range A1:A1000 will automatically adjust to test row 2 for A2, row 3 for A3 and so on.

Hi Rory !

thanks for taking the time to reply.
Unfortunately, I have tried without using the $ sign but it was still not working.
Oh wait I just figured out what I was doing wrong.
I guess sharing and discussing with other people is very helpful :D
My problem is that I was actually selecting the whole column while I should have selected only the range of Data.

Thanks again

 

[DieMaster00]

Hi Rory, hoping you are still monitoring this post. This has been the only post I can find to deal with my issue. I am using 365. I have a range of 4 cells I am trying to format the color of based on what is entered in 1 of the fields.
Ex.
Formula: =K9="P" applies to =$H$9:$K$9 sets color #1
Formula: =K9="M" applies to =$H$9:$K$9 sets color #2
Doing this only sets H9 to the expected color, not the range.

I then tried what was suggested in earlier reply
Formula: =$K9="P" applies to =$H$9:$K$9 sets color #1
Formula: =$K9="M" applies to =$H$9:$K$9 sets color #2
This then applies the color to cells H9-K9 correctly

If I then copy that set of cells to needed rows below, it still works correctly for those columns
Formula: =$K9="P" applies to =$H$9:$K$28 sets color #1
Formula: =$K9="M" applies to =$H$9:$K$28 sets color #2

However, when I then copy that set of columns to other columns, the formulas stay the same, but then the entire set of row cells are set to the color based on K9, not the cell needed for next set of columns, in this case the next one would be O9.
So, what I need is for the set of 4 cells be based on what is in the 4th cell. H9-K9 colored based on what is in K9, L9-O9 based on what is in O9, etc. And need to be able to copy the row 9 down to multiple rows to reference that rows cell, ex. I copied row 9 down to row 10 so H10-K10 uses K10.
And then also be able to copy set of columns H-K to other columns, L-O so O9 is used, P-S so S9 is used.

Hopefully this made sense and you can help.

Thank you!