Excel formula formatting

Question

is it possible to use a formula in conditional formatting to change the fill color of one cell based on the fill color of another cell. All I can find is value based formatting. I have a large sheet that has many rows in one color with nothing in the cell and I want to change those cells to another color without having to select them.

Example sheet

Why not apply a conditional format of no fill if cells are empty? — cybernetic.nomad
You can even search for a color and replace it with another one — usmanhaq
Mr Excel has a UDF for testing for cell formatting. mrexcel.com/forum/excel-questions/… — Frank Ball

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2018-11-12T16:11:34

You could use the Excel 4.0 Macro Function GET.CELL:

Returns information about the formatting, location, or contents of a cell. Use GET.CELL in a macro whose behavior is determined by the status of a particular cell.

Syntax
GET.CELL(type_num, reference)

type_num = 39:
Shade background color as a number in the range 1 to 56. If color is automatic, returns 0.

With cell B1 selected create a named range called FillColour.
This refers to: =GET.CELL(38,A1)

Now, if you enter the formula =FillColour in cell B1 it will show a different value when you change the background colour of cell A1.
NB: You may have to manually force a recalculation for it to work.

Next add conditional formatting rules: =FillColour=29 will be TRUE if the cell to the left of the CF is formatted as purple (RGB: 177, 160, 199).

Excel formula formatting

1 Answers