Conditional Formatting Parts Of Words

Hi all,

I've got a number of cells labelled to express the status of a range of projects: either 'Red', 'Amber' or 'Green'. I've got conditional formatting on these to turn the specific colour, and that's all find and dandy.

However, to reduce cell width, what I'd really like to do is to simply have the first letter of each variable listing - i.e. 'R', 'A', 'G'. Thus the 'ed' of 'Red' would go red, but the 'R' would stay black; the 'mber' of 'Amber' would go amber, etc.

The colour coding is being fed from other workbooks (being completed by other people), and hence I can't simply go and change the original entries to 'R', 'A', 'G'.

Any suggestion on how/if what I want can be done?

TIA,

SamuelT

>>I can't simply go and change the original
Sure you can. Just have a looping macro or a findnext to change Red to R,
Greeen to G, etc.

--
Don Guillett
SalesAid Software
[email protected]
"SamuelT" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> I've got a number of cells labelled to express the status of a range of
> projects: either 'Red', 'Amber' or 'Green'. I've got conditional
> formatting on these to turn the specific colour, and that's all find
> and dandy.
>
> However, to reduce cell width, what I'd really like to do is to simply
> have the first letter of each variable listing - i.e. 'R', 'A', 'G'.
> Thus the 'ed' of 'Red' would go red, but the 'R' would stay black; the
> 'mber' of 'Amber' would go amber, etc.
>
> The colour coding is being fed from other workbooks (being completed by
> other people), and hence I can't simply go and change the original
> entries to 'R', 'A', 'G'.
>
> Any suggestion on how/if what I want can be done?
>
> TIA,
>
> SamuelT
>
>
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile:
> http://www.excelforum.com/member.php...o&userid=27501
> View this thread: http://www.excelforum.com/showthread...hreadid=487180
>

Thanks for the suggestion Don.

However, I can't actually change anything in the original workbooks (darn protection!) and really need something that works within my editable file. Also, there are over 200 projects, so even if I wanted to, it'd take far too long (yeah, I admit it - I'm a lazebag).

BUT, if you can suggest a macro that leaves the first letter of each word, I'd be much obliged! Or, any suggestions to the original conditional formatting query oare more than welcome!

Thanks!

SamuelT

It was not my suggestion to change the un-changeable but to change your
file. It could be as simple as this

Sub changeto1stletter()
For Each c In Selection
c.Value = Left(c, 1)
Next
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"SamuelT" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the suggestion Don.
>
> However, I can't actually change anything in the original workbooks
> (darn protection!) and really need something that works within my
> editable file. Also, there are over 200 projects, so even if I wanted
> to, it'd take far too long (yeah, I admit it - I'm a lazebag).
>
> BUT, if you can suggest a macro that leaves the first letter of each
> word, I'd be much obliged! Or, any suggestions to the original
> conditional formatting query oare more than welcome!
>
> Thanks!
>
> SamuelT
>
>
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile:
> http://www.excelforum.com/member.php...o&userid=27501
> View this thread: http://www.excelforum.com/showthread...hreadid=487180
>

To change the unchangable is, indeed, impossible.

Thankfully your macro works a treat. Many thanks Don.

SamuelT

glad it helped

--
Don Guillett
SalesAid Software
[email protected]
"SamuelT" <[email protected]> wrote in
message news:[email protected]...
>
> To change the unchangable is, indeed, impossible.
>
> Thankfully your macro works a treat. Many thanks Don.
>
> SamuelT
>
>
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile:
> http://www.excelforum.com/member.php...o&userid=27501
> View this thread: http://www.excelforum.com/showthread...hreadid=487180
>

OK - here's how I fixed the problem without using macros:

=IF('Project Status Reports'!$J$17="Green","G",IF('Project Status Reports'!$J$17="Amber","A",IF('Project Status Reports'!$J$17="Red","R","None Recorded")))

SamuelT