CODEWITHSUNDEEP
excelvbaconditional-formatting
JonM
JonM

Reputation: 1

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

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.

Upvotes: 0

Views: 796

Answers (1)

DDV
DDV

Reputation: 2385

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.

Upvotes: 0

Related Questions