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]

Your formula is using a fixed row number, so the formatting for all cells depends on D9 alone. You need:

=$D9="Not on aspirin: has a contraindication - described in comments column"

Note there is no $ sign in front of the 9.

 

[kayak12]

Sigh. That was SO EASY.

THANK YOU. This fixed my problemt, RoryA!

 

[RoryA]

Glad to help. Welcome to the Board, by the way.

 

[Rogster]

Afternoon,

I'm also having an issue with CF only working in the first cell of a row. I would like for a row A#:L# to format red bold italic if there is a date in #L.

Setting up CF via formula, highlighted the cells in the row went to: CF, New Rule, Use Formula..... and tried =IF(ISNUMBER(L9),1,0) and set the format required. The rule applies to =$B$9:$M$9 yet only formats B9 in this instance.

After several hours of trying I'm going to headbutt the desk soon

 

[RoryA]

You must fix the column letter:
=IF(ISNUMBER($L9),1,0)

 

[Rogster]

There are times you hate a "know it all" but in this instance I thank you very much

 

[swaramin]

Hello,

I am having a similar issue with conditional formatting. I am trying to highlight an entire row when a cell from that row is contained in another tab's box of cells. I have used =IFERROR(VLOOKUP($D6,'Annual Update'!B:B,1,FALSE),) to do this in the conditional formatting, manage rules.

The issue I am having is that for the rows where the specific value from the row, in this case, starting with cell $D6, is located within the range where the vlookup is searching, only the first box of the row is highlighted. For example, if D6 is contained in 'Annual Update'!B:B, and where I want F6:P6 highlighted, only F6 is being highlighted.

Any help is very much appreciated.

Thanks Kindly,
Swar Amin

 

[Obied70]

Without seeing how your CF is setup, sounds like you need to apply the CF to the range F6:P6 and not just F6, but again just a guess.

 

[swaramin]

Obied70,

Thanks for the quick response. So I had been messing with this for over an hour now, and within 5 minutes after submitting this post, I was able to realize that I was selecting B:B, not $B:$B, so the vlookup for each additional column would then subsequently search in C:C, D:D, E:E, through all of (F6:P6)...and there was nothing in the other tab's C:C, D:D, etc.

In turn, the following ended up working:

=IFERROR(VLOOKUP($D6,'ANNUAL UPDATE'!$B:$B,1,FALSE),)


Again, thanks for your quick response and help!!

Thanks Kindly,
swaramin