Only some Conditional Formatting & Data Validation ranges work, even though those rules exist

[CaptainKen]

I have several ranges of cells on multiple sheets that I have manually applied both Conditional Formatting (CF) & Data Validation (DV). However, only some ranges actually work. I've also tried this by recording a macro for the CF and then using the macro to save time and avoid any errors. When I go into the respective configuration screens I see the rules.

Example Issues on the FEB tab:

• Ranges G5:G9 work for CF and DV.
• Only DV works for G11:17, but not for CF.
• Neither CF or DV works for G21:G32, G43:41, G43:G49, G51:G63, G65:G70, G72:82 and G86:G93

Would someone please take a look to see what could be causing this as I've spent many hours attempting to redo and trouble shoot. I'm also wondering if it's possible that part of the workbook is corrupt.

Workbook here

 

[awoohaw]

You may want to use a non threatening sharing site like dropbox or onedrive. I do not know what captainken.com is and will not be opening that file.

 

[CaptainKen]

CaptainKen.us is my personal site.

Here's the OneDrive link you suggested.

 

[Peter_SSs]

Depends what the DV is supposed to be doing in each range. Are they supposed to be the same?
The DV in G5 says that column E must be "Variable"
The DV in G21 says that column E must not be "Variable"

BTW, have you looked at XL2BB for providing sample data? It removes any problem with helpers not wanting to or not being allowed to go to other download sites.

 

[CaptainKen]

Great catch on the DV issue. What happened was I copied the value I used in CF, not taking into account the "<>" was supposed to be changed to "=" when used with DV. All the DV ranges have been updated and work as expected.

I went through the CF ranges, only to discover I did something stupid there as well. I had applied the same color that I was using in CF to the cells, before setting up the CF, rather than the default of no color. Changed default color for all to no color and now everything works!

Thank you sir Peter!!!

 

[Peter_SSs]

Glad it is sorted. Thanks for the follow-up.