Home > Enterprise >  Excel - How to set cell color based on a manually selected color (not based on cell value) in a cell
Excel - How to set cell color based on a manually selected color (not based on cell value) in a cell

Time:04-26

I found the below link on how to copy a color based on a cell in the same sheet, however my issue is that the cells which colors have to be matched will be on cells spread out through the workbook.

Link: How to set cell color based on another cell color

My home page (1st sheet) contains a summary of information provided in the following sheets. All the relevant cells have been formatted to reflect the value of their corresponding cells in the other sheets.

So for example, My homepage cell F7 is already formatted so that it copies the value of the corresponding cell in the source sheet "Quarter 1": =quarter1!B15

Now, what I really want is for the reproduced summary cells on my home page to also match the colors I manually select for their corresponding (source) cell the other sheets. Is this possible using VBA coding?

The source cells in question will contain dates which will be manually set - not automatically s based on value, in 3 different colors (red = uncertain; yellow = tentative; green = ok)

I got the below code from another site but it only works if the cells are on the same sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Me.Range("C1").Interior.Color = Me.Range("A1").Interior.Color

End Sub

Is there a way I can tweak this code to refer to a cell color (that is manually selected and not dependent on value) on another sheet in the same workbook?

Thank you so much for your help!

CodePudding user response:

You just need to put the code in "Thisworkbook' with below code and need to change sheet reference instead of me. When you will change the sheet then changes will reflect in other sheet.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Sheet2.Range("C1").Interior.Color = Sheet1.Range("A1").Interior.Color

End Sub
  • Related