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.

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" wrote in message
...
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.

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.


"Bob Phillips" wrote in message
...
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" wrote in message
...
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.

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 wrote:
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.

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.

"FunkySquid" wrote in message
oups.com...
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 wrote:
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.

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 wrote:
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.


"Bob Phillips" wrote in message
...
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" wrote in message
...
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.

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 wrote:
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.


"Bob Phillips" wrote in message
...
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" wrote in message
...
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.

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"


"lightspeed" wrote in message
ups.com...
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 wrote:
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.


"Bob Phillips" wrote in message
...
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" wrote in message
...
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.

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" wrote in message
...
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.


"Bob Phillips" wrote in message
...
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" wrote in message
...
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.

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...

"Bob Phillips" wrote in message
...
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" wrote in message
...
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.


"Bob Phillips" wrote in message
...
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" wrote in message
...
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.