How do I conditionally format cells based on an indirect formula that references "moving" cells

2
votes

Stackoverflow community,

I'm using Excel '10. I currently have an indirect formula that references 4 cells to determine the x,y coordinates for a sum range.

Formula: =IFERROR(SUM(INDIRECT(G27&G26):INDIRECT(H27&H26)),"No Records")

How do I use conditional formatting to highlight the cells that the formula is summing?

1
arraysvbaexcelconditional-formatting
Can you give some examples of what G27, G26, H27, and H26 are? – chancea
G27 and G26 would be the cell coordinates - Ex. G27 = C and G26 = 12; H27 = E and H26 = 9. So the formula above would sum(C12:E9). My question is how to use conditional formatting to highlight cells C12:E9 based on the formula. – Justin

1 Answers

1
votes

you can create a new conditional format for a given cell range which compares the current row and column numbers to the indirect reference cells.

R1C1 style

=IF(AND(ROW(RC)>=R26C7,ROW(RC)<=R26C8,COLUMN(RC)>=R27C7,COLUMN(RC)<=R27C8),1,0)

=ROW(RC) will give you the current row number of the local cell

=COLUMN(RC) will give you the current column number of the local cell.

A1 Style

=IF(AND(ROW(A1)>=$G$26,ROW(A1)<=$H$26,COLUMN(A1)>=COLUMN(INDIRECT($G$27&$G$26)),COLUMN(A1)<=COLUMN(INDIRECT($H$27&$H$26))),1,0)

Since we are using A1 style, you must convert your indirect column reference letters to numbers using COLUMN(INDIRECT()).

In this case, 'A1' is shown in the formula because that is the first cell in the range that I am conditionally formatting. You should change this to whatever the first cell in your range is.