Conditional Formatting based on a cell reference in a formula

[SaraDPE]

I have two rows and several columns per account with up to 40 accounts per worksheet. It is necessary to track invoice amounts and payment amounts relative to the month of occurrence. I want to be able to highlight the invoice cell after it has been paid using the cell name, not the actual amount, as actual amounts may duplicate throughout the reporting cycle. Additionally, the paid cells may cover multiple months of invoices.



For example, if F3 = B2+C2+D2, I want B2, C2, and D2 to automatically be highlighted.



I thought this would be a fairly simple conditional format, but the fact that one Paid sum may include one or more months, is causing issues. Also, since it is the Invoice cells that I would like highlighted, rather than the Paid cells, it is proving more difficult than I can formulate.

 

[Hui]

You can do this manually
Select F2
Press Ctrl+[
Click Yellow


Which you could assign to a Button using this code or run it manually
Select cell F2
Alt+F8 select Highlight_Precedents

Sub Highlight_Precedents()
  With Selection.DirectPrecedents.Interior
  .Pattern = xlSolid
  .Color = 65535
  End With
End Sub

To unhighlight the cells
Select cell F2
Alt+F8 select UnHighlight_Precedents

Sub UnHighlight_Precedents()
  With Selection.DirectPrecedents.Interior
  .Pattern = xlNone
  .TintAndShade = 0
  .PatternTintAndShade = 0
  End With
End Sub