Conditional Formatting doesn't work correctly with Data Validation

[lezboyd]

I am facing an issue in Excel 2010, whereby if I try to apply Conditional Formatting on a selection that also contains cells/columns with Data Validation applied, then it doesn't work as expected.

I am trying to apply Conditional Formatting to a worksheet such that, for all rows where the value of a particular column is "Y", then the row is colored blue. Data Validation is applied in this column for possible values Y/N/Blank only. I have tried to achieve this as under:

An example worksheet is attached.

1. Select all the columns with Data (Columns A thru N).

2. Home tab > Conditional Formatting > New Rule > option "Use a formula to determine which cells to format".

3. Under "Format values where this formula is true", enter =$K2="Y"

4. Click Format button > Fill tab > select light blue color > OK (on format window) > OK (on New Formatting Rule window).

This has the effect of a number of rows getting colored light blue but they are seemingly random rows, and not ones where the value in column K is "Y". I have googled this a lot, and almost all the articles confirm that I am using the right method. But I am not getting the desired results. Please help shed light on what is going wrong.

Thanks & Regards,

Boyd

[#2]

Hi @lezboyd 

Attached please find the updated file, please let me know if it works for you

[#3 · Accepted solution]

@lezboyd 

When you select a range for conditional formatting and trying to create a new rule based on a formula, always pay attention to the row# used in the formula, it must match the row# of the first row in the selected range.

So if you select the range A2:N103 and create a new rule for conditional formatting using your formula for highlighting the rows where column K has a "Y" in it, it will correctly highlight the rows as the first row in the selected range A2:N103 is row#2 and the row# of the cell you are referencing in your formula (=$K2="Y") is also row#2 and they both match.

But if you select the whole columns A:N, the first row in the selected range will be row#1 which doesn't match with the row# used in your formula which is row#2 hence the highlighted rows will be off by one row down.

Also, make sure that you don't have multiple conditional formatting rules for the same rule so it's always a best practice to select all the range once and not applying the conditional formatting to specific range and then using the format painter to copy the same conditional formatting to the other ranges otherwise you will end up with multiple conditional formatting rules.

In the attached, I have applied the conditional formatting based on your formula, see if that works as desired.

[#4]

@Subodh_Tiwari_sktneer 

Thanks for the explanation. All the articles that I googled had examples that gave me the impression that the header row is to be ignored when entering the formula. Changing my formula to =$K1="Y" gave the expected results immediately. Thanks again.

[#5]

@lezboyd 

You're welcome! It seems that you were selecting whole columns and you have tweaked the formula correctly now. Glad you found the explanation helpful.