conditional formatting using lookup up in a different sheet
Sub colourcells()
Dim cell As Range
Dim rng As Range
For Each cell In Worksheets("Sheet1").UsedRange
Set rng = Nothing
On Error Resume Next
Set rng = Worksheets("Sheet2").Cells.Find(cell.Value)
On Error GoTo 0
If Not rng Is Nothing Then
cell.Interior.ColorIndex = rng.Interior.ColorIndex
End If
Next cell
End Sub
--
HTH
Bob Phillips
(remove nothere from the email address if mailing direct)
"ChrisO" wrote in message
...
Hello,
I want to do the following:
- in sheet 2 a column of cells (could be up to 100) contains unique 4
character text codes, e.g. ABXY, each cell is coloured manually by the
user
to any background colour they like.
- in sheet 1 a range of cells each contain one of the 4 character codes.
- I would like to colour the cells in sheet 1 to the same colour as the
corresponding cell for the code in sheet 2.
I know this is going to require VBA which I'm not familiar with, so I'd be
very grateful for your help and suggestions.
Thanks, Chris
|