I want to have a work sheet named "Treatment" have a color key located from G3:G22. I want to be able to change the color for example on G3 to yellow. If the value in G3 is "sun" and I select yellow for the fill color, then that is the master/key for the entire workbook. I want to run VBA to look for any cell in the entire workbook and if it has a value of "sun", then it will automatically fill in yellow for the color.
I have the below code that works well for ONE sheet called "Treatment" and the key is from G3:G22, but it only changes the values in column B. Is there any way to run a similar macro for the entire workbook, not just one sheet?
Sub MatchCellColorbyName()
Dim DataTable As Range
Dim DataCell As Range
Dim DataRangeColor As Long
Dim ColorTable As Range
Dim ColorCell As Range
Dim ValueRange As Range
Dim ValueCell As Range
Dim i As Integer
Dim Count As Long
Count = ThisWorkbook.ActiveSheet.Range("F21").Value
i = Count + 2
Set DataTable = Range("B3: C103") ' Adjust range as needed
Set ColorTable = Range("G3: G22") ' Adjust range as needed
For Each ColorCell In ColorTable
For Each DataCell In DataTable
If DataCell.Value = ColorCell.Value Then
DataRangeColor = ColorCell.Interior.Color
DataCell.Interior.Color = DataRangeColor
End If
Next DataCell
Next ColorCell
For Each ValueCell In ValueRange
ValueCell.Interior.Color = ValueCell.Offset(-1, 0).Interior.Color
Next ValueCell
End Sub
Thanks,
Bookmarks