Highlight Cells based on Active Cell

RSprengers

Board Regular
Joined
Nov 9, 2005
Messages
54
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Try this in the module for the Worksheet:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,212,055
Messages
6,105,639
Members
447,974
Latest member
misspancake

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top