Macro that will color selected cells green if they refer to other worksheet

[lifeskillz]

I am trying to create a macro that will color a selected range of cells green (or any other color for that matter) if the formula in that cell refers to a value in another worksheet.

My current macro colors a cell blue if it has a hardcoded value or black if it refers to another cell within that worksheet. I just don't know how to color the cell green if the formula refers to a cell in another worksheet.

Can anyone help? Thank you!

 

[brettdj]

I do suggest you take a look at my Mappit! addin I referenced earlier if you get a chance

While I know you are okay with the false positives for now, my addin will catch these

Cheers

Dave

 

[mengo6]

Hi Peter,

I've been searching online for a similar macro. I'm looking for something that will color:

1) black if it contains any formula
2) green if it's a simple reference to another cell
3) blue if it's a hard code

This is slightly different from what was described above. Any assistance would be much appreciated!

so if it's =2+2 it would be black. If it's =C2+5 it would be black.
if it's =C2 it would be green

 

[Peter_SSs]

Hi Peter,

I've been searching online for a similar macro. I'm looking for something that will color:

1) black if it contains any formula
2) green if it's a simple reference to another cell
3) blue if it's a hard code

This is slightly different from what was described above. Any assistance would be much appreciated!

so if it's =2+2 it would be black. If it's =C2+5 it would be black.
if it's =C2 it would be green

I'm sorry for the slow response but I have been away from the forum for quite a while. If you are still interested, you could try this adaptation of my earlier macro.

Sub ColourCells()
    Dim cell As Range, SCC As Range, SCF As Range, Test As Range
    Dim CI As Long
    
    With ActiveSheet.UsedRange
        On Error Resume Next
        Set SCC = .SpecialCells(xlCellTypeConstants)
        Set SCF = .SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
    End With
    If Not SCC Is Nothing Then
        SCC.Font.ColorIndex = 5
    End If
    If Not SCF Is Nothing Then
        For Each cell In SCF
            Set Test = Nothing
            On Error Resume Next
            Set Test = Range(Mid(cell.Formula, 2))
            On Error GoTo 0
            If Test Is Nothing Then
                CI = 1
            Else
                CI = 4
            End If
            cell.Font.ColorIndex = CI
        Next cell
    End If
End Sub

 

[mengo6]

I'm sorry for the slow response but I have been away from the forum for quite a while. If you are still interested, you could try this adaptation of my earlier macro.

Sub ColourCells()
    Dim cell As Range, SCC As Range, SCF As Range, Test As Range
    Dim CI As Long
    
    With ActiveSheet.UsedRange
        On Error Resume Next
        Set SCC = .SpecialCells(xlCellTypeConstants)
        Set SCF = .SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
    End With
    If Not SCC Is Nothing Then
        SCC.Font.ColorIndex = 5
    End If
    If Not SCF Is Nothing Then
        For Each cell In SCF
            Set Test = Nothing
            On Error Resume Next
            Set Test = Range(Mid(cell.Formula, 2))
            On Error GoTo 0
            If Test Is Nothing Then
                CI = 1
            Else
                CI = 4
            End If
            cell.Font.ColorIndex = CI
        Next cell
    End If
End Sub

Peter, this is amazing! works perfectly! Thank you!

 

[kanijima]

These conditions are not exhaustive since a formula can exist that does not meet any of those conditions (eg =4*2)

The conditions are also not mutually exclusive - see my first paragraph in post #3. To emphasise - a single formula can refer to ..

a) a cell within the current sheet and a cell in another workbook
b) a cell in another worksheet of the current workbook and a cell in another workbook
c) a cell within the current sheet and a cell in another worksheet of the current workbook and a cell in another workbook
etc

So, for example, if one of the above examples occur, what colour to use?

Taking a guess that ..
If another workbook is involved at all, use red.
Otherwise if another worksheet is involved at all, use green
Otherwise, use black
.. then this may be some use.


Sub AutoColorSelection()
****Dim cell As Range, SCC As Range, SCF As Range
****Dim CI As Long
****
****With Selection
********On Error Resume Next
********Set SCC = .SpecialCells(xlCellTypeConstants, xlNumbers)
********Set SCF = .SpecialCells(xlCellTypeFormulas)
********On Error GoTo 0
****End With
****If Not SCC Is Nothing Then
********SCC.Font.ColorIndex = 5
****End If
****If Not SCF Is Nothing Then
********For Each cell In SCF
************If cell.Formula Like "*.xls*]*!*" Then
****************CI = 3
************ElseIf cell.Formula Like "*!*" Then
****************CI = 50
************Else
****************CI = 0
************End If
************cell.Font.ColorIndex = CI
********Next cell
****End If
End Sub

Hey Peter, just wanted to thank you for this macro, it has been an insane help. I was wondering how we would do something similar if I wanted to italicize all percentages on a worksheet? Maybe we could work that into the code above. I've been struggling to figure this out, but i'm no VBA expert.

 

[Peter_SSs]

.. if I wanted to italicize all percentages on a worksheet?

Welcome to the MrExcel board!

Does this do what you want?

Sub Percent_Italic()
  Dim cell As Range
  
  For Each cell In ActiveSheet.UsedRange
      If cell.NumberFormat Like "*%" Then cell.Font.Italic = True
  Next cell
End Sub

 

[kanijima]

Welcome to the MrExcel board!

Does this do what you want?

Sub Percent_Italic()
  Dim cell As Range
  
  For Each cell In ActiveSheet.UsedRange
      If cell.NumberFormat Like "*%" Then cell.Font.Italic = True
  Next cell
End Sub

Yes, this is perfect... just wondering if there is a way to tie this into the color coding one for one overall macro.

Thanks so much, you have been a huge help.

 

[sush23]

Hey Peter, this was so helpful! I was wondering if you can help me tweak it a little bit. I need to have cells that reference the same work sheet as green, and cells that reference other worksheets as black. Also is there any way of including something to be able to automatically update the color, say I had a formula which would make it black, then I decide to hard code that cell, is there any way to turn it blue automatically without running the macro again?

Thank you
Rachel

 

[Peter_SSs]

Clarifying, you want ...

- blue if hardcoded (constant)?

- green if a formula references its own worksheet?

- black if a formula references other worksheet(s)?

1. What is to happen if a cell references the same worksheet AND another worksheet?
eg =VLOOKUP(D2,'Budget Worksheet'!A2:B10,2,0)

2. What is to happen if a cell contains a formula that doesn't reference any cells at all?
eg =2+2

 

[sush23]

Peter,

Thanks for your prompt response! Answers to your questions:

1) I would just be black, since it's a formula. I would only want it to be green if it directly links to the active worksheet, otherwise black.
2) It would still be black since it's a formula

Text would also be black, formatting would only apply to numbers

Thank you for your time.

Rachel