Change color in sheet1 based on cell colors in sheet2

amit_kakad 

If the cells in the range C2:C11 in "Sheet2" are colored manually, and you want cell A1 in "Sheet1" to change its fill color based on the presence of specific colors (Green, Orange, Red), you can use a VBA (Visual Basic for Applications) macro to achieve this.

Here's how you can set it up (code is untested):

Sub ColorBasedOnRange()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim cell As Range
    Dim colorGreen As Long
    Dim colorOrange As Long
    Dim colorRed As Long
    Dim maxColors As Long
    
    ' Define your desired colors as RGB values
    colorGreen = RGB(0, 176, 80)    ' Green
    colorOrange = RGB(255, 192, 0)  ' Orange
    colorRed = RGB(255, 0, 0)      ' Red
    
    ' Set the worksheet references
    Set ws1 = ThisWorkbook.Sheets("Sheet1")  ' Change to your sheet name
    Set ws2 = ThisWorkbook.Sheets("Sheet2")  ' Change to your sheet name
    
    ' Initialize the maxColors variable
    maxColors = 0
    
    ' Loop through the specified range in Sheet2 (C2:C11)
    For Each cell In ws2.Range("C2:C11")
        ' Check the background color of each cell
        If cell.Interior.Color = colorGreen Then
            maxColors = Application.WorksheetFunction.Max(maxColors, 1)
        ElseIf cell.Interior.Color = colorOrange Then
            maxColors = Application.WorksheetFunction.Max(maxColors, 2)
        ElseIf cell.Interior.Color = colorRed Then
            maxColors = Application.WorksheetFunction.Max(maxColors, 3)
        End If
    Next cell
    
    ' Apply fill color to cell A1 in Sheet1 based on maxColors
    Select Case maxColors
        Case 1
            ws1.Range("A1").Interior.Color = colorGreen
        Case 2
            ws1.Range("A1").Interior.Color = colorOrange
        Case 3
            ws1.Range("A1").Interior.Color = colorRed
        Case Else
            ' Default color if none of the specified colors are found
            ws1.Range("A1").Interior.ColorIndex = xlNone
    End Select
End Sub

Now you can run this macro whenever you want to update the fill color of cell A1 in "Sheet1" based on the colors of the cells in the range C2:C11 in "Sheet2.".

The macro will check the colors in the specified range in "Sheet2" and fill cell A1 in "Sheet1" with the appropriate color (Green, Orange, or Red) based on the detected colors in the range. If none of these colors are detected, it will remove any fill color from cell A1. 

amit_kakad 

In your screenshots, C2:C11 are colored instead of C1:C10.

Will the cells be colored manually by the user, or do you use conditional formatting rules to color the cells?