Excel 2010 Conditional Formatting - INDIRECT Function

1
votes

Excel SpreadsheetI have an ongoing table that contains info in columns A-K and an undefined amount of rows. I'm trying to create a rule that will highlight columns A-K in a specific row if the value of cell D in that row is less than or equal to a specific date.

I have tried "Use a formula to determine which cells to format" with this formula: =INDIRECT("D"&ROW())<"DATE" and applied it to $A:$K

This turned my entire chart orange...

I've also tried "Format only cells that contain" with this formula: "Cell Value less than or equal to" =INDIRECT("D"&ROW())="DATE" and applied it to $A:$K

with the same effect...

Can anyone help me to get this corrected?

1
formattingexcel-2010conditionalexcel-indirect
Use =AND($D1>0,$D1<="Date") as the formula. – Phylogenesis
Better...it didn't change the rows that don't have a value in the D cell, which is partly what I wanted. However, it's still changing the rows where the value of the D cell is prior to today's date.. – Kay
Try =AND($D1>0,$D1<=DATEVALUE("Date")) – Phylogenesis
That one didn't change anything... I added an image to the question. I want the row to be orange until I reach the date in the D cell. So row 6 should be orange, but row 4 should be black. Basically, I want a visual alert that I've reached that date so that I know when to contact. – Kay

1 Answers

0
votes

I just figured it out:

=$D1>TODAY() format font to orange

=$D1<=TODAY() format font to black

Thank you @Phylogenesis for your help! I really appreciate it!