Colour cell if used in a new formula

[Daktari]

Hi again people,

I've an odd question (again):
I'm trying to change the background colour of a cell if the cell is used in a formula on a different sheet.

My idea was to use the code below to change the cell colour when I clicked the cell:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Selection.Interior.ColorIndex = xlNone Then
With Selection.Interior
.ColorIndex = 43
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
Selection.Interior.ColorIndex = xlNone
End If
End Sub

BUT, it doesn't work when I'm selecting cells as a part of a simple =+sheet4!c3+sheet4!d4 etc function. Is there an easy fix?

Alt. my backup plan is to change the cell colour if it's used in a formula in a specific sheet, but searches for that doesn't get me close...

(My task is to link sub categories to categories, and I don't want to use a sub category twice. It's a lot of sub categories, so I'm trying to device myself a tool).

Thanks in advance.

 

[lrobbo314]

How about this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call HighlightFormulas
End Sub

Sub HighlightFormulas()
Dim ws As Worksheet
Dim cel As Range, r As Range
Set ws = ActiveSheet
Set r = ws.Cells.SpecialCells(xlCellTypeFormulas)
For Each cel In r
    If InStr(1, cel.Formula, "Sheet4") > 0 Then
        cel.Interior.ColorIndex = 43
    End If
Next cel
End Sub

 

[Daktari]

I'm not at work now, so I just tried the code in a new workbook at home.

edit: I just got your code to work. It changes the cell with my formula. Is it possible to revert it to colour the cells which make up the formula instead?

 

[lrobbo314]

How about this.

Sub xL()
Dim r As Range, cel As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set r = ws.Cells.SpecialCells(xlCellTypeFormulas)
For Each cel In r
    Range(cel.Formula).Interior.ColorIndex = 43
Next cel
    
End Sub

Also, Excel's built in Trace Precedents feature would probably accomplish the same goal.

 

[lrobbo314]

Sorry, that last one wont work,

Hopefully this one will.

Sub xL()
On Error Resume Next
Dim r As Range, cel As Range
Dim ws As Worksheet
Dim ctr As Integer, St As Integer
Set ws = ActiveSheet
Set r = ws.Cells.SpecialCells(xlCellTypeFormulas)
For Each cel In r
    St = 1
    ctr = (Len(cel.Formula) - Len(Replace(cel.Formula, "Sheet", ""))) / 5
        For i = 1 To ctr
            St = InStr(St, cel.Formula, "Sheet")
            Range(Mid(cel.Formula, St, 9)).Interior.ColorIndex = 43
            St = St + 9
        Next i
Next cel
    
End Sub

 

[Daktari]

Sorry for the delay in response. It doesn't do anything to the worksheet at all.

I'm pasting it in the view code VBA thingy, with and without

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call xL 
End Sub

It is supposed to change the cell colour if the cell is used in a formula on a different specific sheet right? For completeness, the sheet with the cells I'd hope will be coloured is named "Skjema", and the sheet with my formulas is "Ark1". (I've changed the names inside your code to check for the name difference though).

I think it's a user error on my part.