Conditional Formatting Question

[Xearo96]

Hi Everyone,

I'm stumped.

I am trying to use conditional formatting to turn a specific cell red, if a certain date is found in an associated cell. In my head, it was simple:

Column A has a series of names (A2 to A12). Each name has a row of dates associated with it ranging from columns B to AL. I am using another cell (B17) as an input cell. Once a date is typed in this cell, the conditional formatting uses a formula to scan the row beside each name and if a true value is found, the cell turns red.

Under conditional formatting, I used "Formula" and for the first name, cell A2, I have =OR(B2:AL2=$B$17). It works perfectly for cell A2.

However, for the next name in the list, cell A3, I apply the same formula, adjusted for the correct row, and it doesn't work.

What am I missing?

 

[Joe4]

You have your rule messed up.
The Range to apply it to should be your whole range you want formatted, i.e. B2:AL2

Then your formula should be written so that it is relevant to the FIRST cell in your applied Range. Excel is smart enough to adjust it for the other cells.
So your formula should look like:

=B2=$B$17

 

[Xearo96]

Thank you for the incredibly speedy response, it is very appreciated.

However, changing the range to B2:AL2 will color the cell where the date is found. I am only trying to turn the name in column A2 red, not the date itself.

 

[Joe4]

Sorry, I misunderstood.

However, the same rule of Logic applies:
1. Select the entire range you want the Conditional Formatting to apply to: A2:A12
2. Write the formula as it applies to the FIRST cell in that range.

So I think you could use a formula like:

=COUNTIF(B2:AL2,$B$17)>0

 

[Xearo96]

Amazing, thank you.

Update: This seems to work now, however, it looks like I simply need to write one condition per row, since I only want A2 to change based on the results of B2:AL2, A3 based on the results of B3:AL3, and so on, so forth. When I apply the formula above to the entire range A2:A12, the names of A3, A4, etc change based on the results of row 2, and that's not what I'm looking for.

Either way, it's easy to copy the condition 11 times.

Again, I can't thank you enough for your help.

 

[Joe4]

Amazing, thank you.

Update: This seems to work now, however, it looks like I simply need to write one condition per row, since I only want A2 to change based on the results of B2:AL2, A3 based on the results of B3:AL3, and so on, so forth. When I apply the formula above to the entire range A2:A12, the names of A3, A4, etc change based on the results of row 2, and that's not what I'm looking for.

Either way, it's easy to copy the condition 11 times.

Again, I can't thank you enough for your help.

No, if you follow my instructions EXACTLY, and use the EXACT same formula I provided, you will NOT need to repeat it at all.

Just like when you enter a formula in a cell and copy it down, Excel automatically adjusts the ranges, it will do the same here, as long as you properly use Relative/Absolute Cell referencing in your formula properly.

Any column or range that you place a "$" in front of "locks" that reference down, so it won't change as you move down and across. However, if you do NOT put it there, it will flow with your range changes.

So if you select the range A2:A12 and enter the CF formula of:

=COUNTIF(B2:AL2,$B$17)>0

Then, cell A2 will use the formula:

=COUNTIF(B2:AL2,$B$17)>0

and cell A3 will use:

=COUNTIF(B3:AL3,$B$17)>0

and cell A4 will use:

=COUNTIF(B4:AL4,$B$17)>0

etc.

Try it, and you will see it works!
Note that if you view the CF formatting rule afterwards, it will always show the formula as it applies to the first cell in your applied range - don't let that confuse.
You will see it works as it is suppsoed it.

See here for more on Absolute/Relative cell referencing: Absolute, Relative, and Mixed Cell References in Excel

 

[Xearo96]

.... and it sure does. It was my mistake, I copied the formula exactly but made a mistake in the dates I arbitrarily chose as test cases.

Thanks gain, have a great day!

 

[Joe4]

You are welcome!

Note that there is a "format copier", but I find that to be rather unpredicable, so I never use it.
I always use this method, where I select the whole range I want to apply it to, then write the formula as it applies to the first cell in that range.

And by the way, welcome to the board!