Multiple sheet formatting based on cell info [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
792
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!