How to make secondary cells color of primary

[Closed]
Report
-
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
-
Hello,

Is there a way to make cells automaticaly change to the color I chose for another? Example: I decide to make cell A1 red. When I do this cells B1:Z1+C7 and E74 automatically turn red also.

Thanks

1 reply

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
486
Hi markhackney,

Yes this is possible, but what is the relation between A1 and B1:Z1+C7+E74.

For the following code I assumed that A1 could be any cell and that B1:Z1 is dependent of the row of the selected cell (so if A2 is selected then it will be B2:Z2). C7 and E74 are fixed locations.

First select the cell you would like to copy the color from and then run the following code:
Sub Test()
Dim aColor, aRow As Integer
aColor = ActiveCell.Interior.ColorIndex
aRow = ActiveCell.Row

Range(Cells(aRow, "B"), Cells(aRow, "Z")).Interior.ColorIndex = aColor
Range("C7").Interior.ColorIndex = aColor
Range("E74").Interior.ColorIndex = aColor
End Sub

Best regards,
Trowa