Cell Interior Color formatting based on other cells' value AND interior color in row

Question

I am trying to make an existing large spreadsheet easier to read for a variety of end users. I cannot share the actual spreadsheet, but have made this example page to show what I am trying to do.

https://ibb.co/VY3c8c8

I want to edit the background color of each cell in column A based on the value and color of the cells in that row on the other end of the spreadsheet. For example, if every cell in C1:G1 that value = X or O and is colored green, set A1 fill to green.

I feel like I am missing a simple, key piece of information, but have not had any luck finding what it may be.

I have tried a wide variety of conditional formatting, but believe that to be impossible since you can't pull Interior.Color into the formula.

I have tried a wide variety of vba, with varying level of success, but haven't found anything that completely works. Here are a couple resources I've had some luck using:

https://www.bluepecantraining.com/portfolio/excel-vba-macro-to-apply-conditional-formatting-based-on-value/

https://social.technet.microsoft.com/Forums/en-US/2fffa4d8-bbba-473b-9346-5fce8f0728a8/using-vba-to-change-a-cell-colour-based-on-the-information-in-a-different-cell-excel-2010?forum=excel

VBA checking cell interior color

Finding cells with certain interior color

At this point, my coding attempts are so jumbled, I do not have any meaningful code attempts readily available for pasting here.

Dean Dean · Accepted Answer · 2019-07-24T15:18:12

When using interior cell colours you have two options in VBA. First .Interior.Color, second Interior.ColorIndex.

Now important to note that if you are wanting to check if a cell is a certain colour you are going to need a uniform approach such that all your users use exactly the same colour. For example, using the default dark green option in Excel.

Color: Debug.Print ActiveCell.Interior.Color returns 5287936 in the immediate window. ColorIndex: Debug.Print ActiveCell.Interior.ColorIndex returns 14 in the immediate window.

If we are to use this information we can then simply code an if statement to read:

If Sheets("Sheet1").Range("C1").Interior.ColorIndex = 14 And Sheets("Sheet1").Range("C1").Text = "X" Then
    Sheets("Sheet1").Range("A1").Interior.ColorIndex = 14
End If

Again, to reiterate, the colours used will need to be uniform for you to pick them up with your VBA. Additionally you now write a simple double For loop to complete your task with colours and text as your verification's to colour Column A.

Cell Interior Color formatting based on other cells' value AND interior color in row

1 Answers