Conditional Formatting Parts Of Words

[SamuelT]

Hi all,

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

However, to reduce cell width, what I'd really like to do is to simpl
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; th
'mber' of 'Amber' would go amber, etc.

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

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

TIA,

Samuel

 

[Don Guillett]

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.

 

[SamuelT]

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

 

[Don Guillett]

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

 

[SamuelT]

To change the unchangable is, indeed, impossible.

Thankfully your macro works a treat. Many thanks Don.

SamuelT

 

[Don Guillett]

glad it helped

 

[SamuelT]

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

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

Samuel