conditional formatting

[merlin]

I had been formatting a cell colour to pink green or blue depending on if it
contained E, M or L.

I've now had to change my cell contents to E1, E2, E3, E4 etc instead of
just 'E' so I've gone over my limit of 3 conditional formats.

Is there a way I can conditionally format based just on the letter E M or L
and not the number with it?

Or can you think of another way to achieve the same thing?

Much obliged for any help suggested.

 

[Bob Phillips]

How about using a formula of

=UPPER(LEFT(A1,1))="E"

etc

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[merlin]

will that work in the conditional formatting statement? I think it has to be
generic so I'm not sure how to reference the cell it applies to in that
context.

 

[FunkySquid]

If you highlight the range of cells you want to colour, you could use
this code instead:

Sub ColourCells()
Const EColour As Integer = 7 'pink
Const MColour As Integer = 10 'Green
Const LColour As Integer = 5 'blue
Dim myCell As Range

For Each myCell In Selection
Select Case Left(myCell, 1)
Case "E"
myCell.Interior.ColorIndex = EColour
Case "M"
myCell.Interior.ColorIndex = MColour
Case "L"
myCell.Interior.ColorIndex = LColour
End Select
Next myCell

End Sub


FunkySquid

 

[merlin]

Ah...macros are well beyond me I'm afraid.

I was hoping for a simple comparison phrase such as 'E1 OR E2 OR E3 OR E4'
but I can't seem to get that to work.

 

[lightspeed]

Conditional formatting can be done in a non-generic way. What you do
is format with Bob's code in cell A1, and then use the format painter
to copy the conditional formatting to the remaining cells below. The
A1 reference will automatically update to B1, C1, D1.... as the
formatting is painted on down.

 

[lightspeed]

Conditional formatting can be done in a non-generic way. What you do
is format with Bob's code in cell A1, and then use the format painter
to copy the conditional formatting to the remaining cells below. The
A1 reference will automatically update to B1, C1, D1.... as the
formatting is painted on down.

 

[merlin]

OK - halfway there.

The cell references are updating but the cell colour isn't changing.

My first cell of data (top left) is D5 so my statement reads:

=UPPER(LEFT(D5,1))="E"

 

[Bob Phillips]

Yes it will, just select all of your target cells (I have assumed A1 is the
first selected), and then in CF change Condition1 to Formula Is, and add the
formula.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[merlin]

Magic!

I didn't realise the 'formula is' referred to the condition - I thought it
referred to the nature of the cell contents.

You're all brilliant - thanks to all who dug me out of that one...