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.
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
