Forum Discussion

drbradley's avatar
drbradley
Copper Contributor
Feb 09, 2023
Solved

Flash Fill Conditional Formatting?

 

 

I'm working on a tracking sheet for when certain items are done for a new hire's profile. I currently have the due date column populated by a formula based upon the hire date. The completed column is manually entered, but is then formatted based upon a formula that compares the dates. Each new hire will have their own column of due dates and completed dates. I've attached images of both the sheet as it's in use as well as with the formulas I've used shown. The conditional formatting is based on formulas with mixed references. 

What I'm having trouble with is how to easily apply this conditional formatting to new columns as more new hires come on board. Since Column D references Column C and Column F references Column E and so on, I haven't found an easy way to apply this to a sheet or even multiple columns. Is there an easy way to get this done with how I've started or is there another formula or method I should be using entirely?

 

excel
Formulas and Functions
office 365
  • dscheikey's avatar
    dscheikey
    Feb 10, 2023

    drbradley 

    Please try this with the conditional formatting. You must not use the $ in the formula.

    =AND(ISEVEN(COLUMN()),ISBLANK(D8))
=AND(ISEVEN(COLUMN()),C8<D8)
=AND(ISEVEN(COLUMN()),C8>D8)

Applies to:
=$D$8:$ZZ$25

     

2 Replies

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Feb 10, 2023

    drbradley 

    Please try this with the conditional formatting. You must not use the $ in the formula.

    =AND(ISEVEN(COLUMN()),ISBLANK(D8))
=AND(ISEVEN(COLUMN()),C8<D8)
=AND(ISEVEN(COLUMN()),C8>D8)

Applies to:
=$D$8:$ZZ$25

     

    • drbradley's avatar
      drbradley
      Copper Contributor
      Feb 14, 2023

      dscheikey Thank you! That is working so far! I did have an issue where if a date was present in the first column, but not the second the box would turn green instead of orange, but making that rule first and stopping rule running if true corrected the issue. 

       

      Could you explain to me what you did so I can apply it again in the future?

       

Resources