Multiple sheet formatting based on cell info

Closed
3dinc - May 1, 2010 at 08:53 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - May 2, 2010 at 10:53 PM
I have multiple spreadsheets. I want to creat a formula/format so that when I color a particular cell on the first sheet, any cells on subsequent sheets containing the same info/text, would become formatted in the same color.

How do I do this???

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 2, 2010 at 10:53 PM
see whether this macro "test" will help you
you have to give input in the two input boxes

Sub test()
Dim color As Long, j As Integer, sh As String
 Dim r As String, k As Integer, ws As worksheet
 Dim cfind As Range, x
 sh = InputBox("type the name of sheet you want to conider first, e.g. sheet1")
r = InputBox("the cell you want to select, e.g. C2")
j = Worksheets.Count
Set ws = Worksheets(sh)
sh = ws.Name
ws.Activate
ActiveSheet.Range(r).Interior.ColorIndex = 3
x = Range(r).Value
For k = 1 To j
If Worksheets(k).Name = sh Then GoTo nextk
With Worksheets(k)
Set cfind = .Cells.Find(what:=x, lookat:=xlValue)
If cfind Is Nothing Then GoTo nextk
cfind.Interior.ColorIndex = 3
End With
nextk:
Next k

End Sub


Sub undo()
Dim j As Integer, k As Integer
j = Worksheets.Count
For k = 1 To j
Worksheets(k).Cells.Interior.ColorIndex = xlNone
Next k
End Sub
0