Conditional Formatting: Format Cell if Indicator on first cell in column AND no formula

Hi All,

I'm looking for create a conditional formatting rule that formats a cell (fills cell w/ yellow background shading) if two conditions are both met:

1) in row 1 of whatever column the cell is in, the cell must contain the letter E hard-coded
2) in the cell to be formatted, the contents must be-hard coded (cannot be a formula)

I think I might be close to getting it right, but something is missing and I could use the Forum's help. Here is what I've done so far:

1) created a new conditional formatting rule that applies to the entire worksheet
2) used this formula to determine which cells to format:

=AND(RIGHT(A$1,1)="E",CellHasFormula=FALSE, ISNUMBER($1:$1048576))

3) determined that formula applies to all rows (=$1:$1048576)


I have attached the workbook I'm using, and if you look at the rules manager, you can see what I've attempted to do. If this formatting rule works as intended, cell I4 should be yellow/blue font (because it's hard-coded and contains an E in cell I1), cell I3 should not be formatted (it contains a formula), and cells H3/H4 should also not be formatted (they do not contain an E in cell H1).

Any thoughts/assistance are very much appreciated, and I thank you for taking the time to read my question.

Best,

RK

I assume you also don't want the first row highlighted (even though the E's are hard-coded. Also, you say "If the cell CONTAINS an E", but your example is only if it ENDS in an E. If both are true, then try this:

=AND(RIGHT(A$1,1)="E",ISFORMULA(A1)=FALSE,A1<>"",ROW()<>1)

If it's that the cell in the first row CONTAINS an E, then try this:

=AND(ISNUMBER(SEARCH("E",A$1)),ISFORMULA(A1)=FALSE,A1<>"",ROW()<>1)

Gregb11,

Thank you so much for your prompt/helpful reply. That was exactly what I needed, and now using this I can go and implement other formatting in my sheet. Thank you so much for the explanation! Marking thread as solved.

Best,

RK

You're welcome. Glad it worked.