Excel dynamic conditional formatting based on cell

问题:

A few months back I posted the following question Conditional formatting rows based on cell dynamic.

At the time the formula provided was fantastic and still is however I'm redeveloping the spreadsheet and need something similar but I just can't figure out a solution.

I currently have a list of times in column A in increments of 1 hour from 9am to 9pm. In column B I would type 2 next to 9am it would somehow highlight 9am and 10am, now under that say you type 7 next to 11am it would highlight 11am, 12pm 1pm 2pm 3pm 4pm 5pm.

Hope that makes sense

Many Thanks

回答1:

This would seem to require the Use a formula to which cells to format option when creating a new CF rule.

                 

Select A2:B14 with A2 as the Active Cell and choose Home ► Styles ► Conditional Formatting ► New Rule. Select the Use a formula to which cells to format option and supplt the following for Format values where this formula is true:

=AND($A2>=INDEX($A$2:$A$14,MATCH(1E+99,$B$2:$B2)),$A2<(TIME(VLOOKUP(1E+99,$B$2:$B2,1),0,0)+INDEX($A$2:$A$14,MATCH(1E+99,$B$2:$B2))))

Click Format and select some formatting. I chose an orange Fill. Click OK to save the formatting choice(s) and then OK again to create the new rule. You should be left with something similar to the above sample image.