Formating Macro code

[jamie.gill]

I need a Code to put on to my sheet so if a cell contains a line of text such as "france" it will turn blue...

also id like to do this for multiple different titles such as italy, wales, england, scotland and ireland...
all of these to come up in different colours on entry...

 

[Peter_SSs]

If you have Excel 2007+ you could use Conditional Formatting.

Otherwise,

1. Do you have a full list of countries and corresponding colours?

2. Are the countries being entered in a particular column or columns? Which one(s)?

 

[jamie.gill]

it applies to the whole sheet

also its like wales = red, england=grey, ireland = green, france = blue, scotland = dark blue

 

[jamie.gill]

so like is there a sheet code??

 

[Peter_SSs]

Try this in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try entering/deleting/editing/copy-pasting cells in your sheet.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> rCel <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> iCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rCel <SPAN style="color:#00007F">In</SPAN> Target.Cells<br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> UCase(rCel.Value)<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "WALES": iCol = 3<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "ENGLAND": iCol = 15<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "IRELAND": iCol = 4<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "FRANCE": iCol = 33<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "SCOTLAND": iCol = 5<br>            Case Else: iCol = xlNone<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>        rCel.Interior.ColorIndex = iCol<br>    <SPAN style="color:#00007F">Next</SPAN> rCel<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>