Highlight Cells based on Active Cell

[RSprengers]

I have a spreadsheet with data.

Below my datamatrix, I have cells showing results of calculations of different cells in the matrix.

I want to do this:

If I click on a result, I want the cells that are used in the calculation to be highlighted. Once I click in another result, I want to 'unhighlight' the previous highlights and highlight the cells used in the newly selected calculation

Any ideas?

Thx

 

[Peter_SSs]

RSprengers

Welcome to the Mr Excel board!

I am not sure that this is quite what you want but might be worth a try while waiting for other suggestions. This is avialable in Excel 2002 but I am not sure about earlier (or later) versions.

On the menus go Tool|Formula Auditing|Formula Auditing Mode. On your sheet you will now see formulas rather than their results, but clicking on a formula will immediately highlight the cells that feed into it.

 

[RSprengers]

Thanks for the reply, but this is not what I'm looking for.

I want to show users of my sheet HOW I came to a certain result by showing them the values used in the calculation (Prefferable green if I added the value in the calculation / Red if I subtracted the value)

So, say my results are showing in a20-a30, when I click a20, I want Excel to highlight b7, d1, e12
When I click on a25, I want Excel to bring b7, d1 and e12 back to normal and highlight the cells used in the calculation in a25.

I don't have to 'read' the cells from the calculation, I know which cells are used in each calculation, I just want them to be highlighted when the result cell is the active cell.

So basically what I want to do is highlight a number of cells based on the status of another cell.

Hope this is more clear

 

[Peter_SSs]

RSprengers

Yes, I think that it is clear enough - I just cannot think of a way to do it. There are some very clever minds lurking around this board, so hopefully somebody else will come up with something to assist you.

Just another suggestion in the absence of the perfect answer:
1. Select the cell in question.
2. Tools|Formula Auditing|Trace Precedents

If you do much of this it would be worth Tools|Formula Auditing|Show Formula Auditing Toolbar and work from it.

 

[Andrew Poulsom]

Try this in the module for the Worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static OldCell As Range
    Application.EnableEvents = False
    If Not OldCell Is Nothing Then
        If Not Intersect(OldCell, Range("A20:A30")) Is Nothing Then
            OldCell.Precedents.Interior.ColorIndex = xlNone
        End If
    End If
    Set OldCell = Target.Cells(1, 1)
    If Intersect(Target, Range("A20:A30")) Is Nothing Then
        Application.EnableEvents = True
        Exit Sub
    End If
    Target.Cells(1, 1).Precedents.Interior.ColorIndex = 35
    Application.EnableEvents = True
End Sub