Question:

Hi, I am having trouble applying conditional format in Excel 2007 based on value of column heading.  I have attached a sample file.  In this example, I’d like to highlight the columns that does not have heading starts with the letter M.  I tried the left function but no luck.  In addition, is it possible to dynamically lock and unlock cells based on the conditional format?  Please see attached.  I renamed it with txt extension.  Please rename with xlsx extension.  Thanks.

#N/A       M1        M2        M3
100      300      100      300
200      250      200      250

Solution:

I’m confused now……

Based on the original attachment from JCJG the cells should be formatted when header doesn’t begin with “M” (although it’s not clear whether there could be headers which aren’t #N/A but don’t begin with “M”)

For that you could use the second formula I suggested, i.e. select the range A4:D5 and use

=IF(ISERROR(A$3),1,LEFT(A$3)<>”M”)

regards, barry