1

I am having trouble coloring cells in a column (if a cell D2 is light green, then B2 is colored light green). I have tried using conditional formatting, and looked at Changing Color of a column based on other column in put

However, I do not know what to put in formula to say that a cell D2 is light green. Let me know if I broke any rules here, and I'll fix.

cd3091
  • 67
  • 7

2 Answers2

2

Like I said many times before: Color is not data. There is no worksheet formula or conditional formatting rule that can evaluate the color of a cell.

The reason or logic for that manually applied color is in a person's head, but not derivable from data in the spreadsheet.

Use real data in the spreadsheet if you want to use conditional formatting or worksheet formulas.

If you want to evaluate cells by their color and apply that same color to another cell, you will need to use VBA.

teylyn
  • 34,374
  • 4
  • 53
  • 73
1

Apart from using vba, if you can tolerate the following:

  • manually refresh the conditional formatting for Column B each time you change the colour in Column D
  • save and continue to use your workbook as .xlsm (macro enabled workbook)

then try the following:

Please note I used the following sample data (starting from the first row) where Column A serves as Column D in your question:

Sample Data

In the Name Manager, set up a name called GetCellColour with the following formula:

=GET.CELL(63,$A1)

Replace $A1 with $D2 or the actual cell reference in your real case. This should be cell that will trigger the conditional formatting in B2.

Set a light green colour in cell A1, and in a blank cell say C1 enter the following formula:

=GetCellColour

In my example the colour code returned by the above formula is 35 for light green.

Highlight Column B (or the relevant range in Column B that you want to apply the conditional formatting rule) with cell B1 being the active cell, go to Conditional Formatting function to set up the following formatting rule:

=GetCellColour=35

Then your cells in Column B will be highlighted by light green colour if the corresponding cell in Column A is colored in light green. Please note, if you changed the cell colour in Column A, you need to go to Data tab to Refresh the worksheet to "update" the conditional format in Column B.

Here is a live demo:

Live Demo

For the use of GET.CELL function in the name manager, you can give a read to this article.

Let me know if you have any questions. Cheers :)

Terry W
  • 3,199
  • 2
  • 8
  • 24
  • There are drawbacks with Get.Cell. It's not supported on modern devices or the web, since it's an ancient macro command, and it recognises only 56 of the millions of colours that Excel can use today, so this method can lead to errors. See https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color/24384324#24384324 – teylyn Nov 22 '19 at 01:36