VBA accessing named ranges that are referenced by an active cell's index formula value

[frisbeenut]

I have in column D on a sheet titled "Budget", various references to a variety of different named ranges using the index function, similar to "=INDEX(GasBill, 3)" . Those named ranges are defined on a sheet titled "Bills". I would like to have a macro that when the active cell is in column D of the Budget sheet, and that active cell refers to a valid named range in its formula, I would like to have the macro change the font color to red in the current active cell on the "Budget" sheet, and change the font color to red in cell on "Bills" sheet that the formula refers to. (Note: in the "=INDEX(GasBill, 3)" formula, the 3 does not correspond to the third column on the Bills sheet.)

I would like this macro to be able toggle the font colors of those two cells in both the Budget and Bills sheets to red if the font color is black, or change to black if the font color is red. (If the Budget cell's index formula refers to named range that is not defined on the Bills sheet, then only highlight only the cell or cells on the Budget sheet.)

Lastly, it would be fantastic if this macro could work on a range of selected cells, and only gather the index formula's values from column D and highlight multiple index formula references. (and possibly work if the active cell is not in column D but entire rows on the Budget sheet are highlighted)

Thanks so much

 

[p45cal]

To save us time, could you attach a workbook with this arrangement?

 

[frisbeenut]

To save us time, could you attach a workbook with this arrangement?

 

[p45cal]

The attached has a go at the first part, although if the reference is not valid it does not highlight the cell on the Budget sheet.

 

[frisbeenut]

The attached has a go at the first part, although if the reference is not valid it does not highlight the cell on the Budget sheet.

Wow, that certainly is interesting and difficult as well. How difficult will it be to change how it behaves? The way I need it to work is to select the cell (or cells) before executing the macro by using a command button. and to stay red, unless I press the command button again, changing it back to black again. Lastly, is it possible to make the code in one sub? (or perhaps I do not full understand how it is working)

Thanks, and what you created is truly awesome.

 

[frisbeenut]

I am not sure I am handling error correctly, nor am I sure my coding is best either, but it seems to work. Anyone have improvements or better alternative coding, all suggestions are appreciated.

Sub Test1()
Dim Cell As Range
Dim RefCell As Range
  For Each Cell In Selection
    On Error Resume Next
    Set RefCell = Evaluate(Cell.Formula) 'how to handle if error
    On Error Resume Next
        If Len(Cell.Text) > 0 Then
           If Cell.Font.Strikethrough = True Then
              Cell.Font.ColorIndex = -4105 'automatic
              Cell.Font.Strikethrough = False
              RefCell.Font.ColorIndex = -4105 'automatic
              RefCell.Font.Strikethrough = False
           Else
              Cell.Font.ColorIndex = 3 'red
              Cell.Font.Strikethrough = True
              RefCell.Font.ColorIndex = 3 'red
              RefCell.Font.Strikethrough = True
           End If
        End If
  Next
End Sub

 

[p45cal]

Again, only to give you some ideas, see attached.

 

[frisbeenut]

I am currently using the following version of the code above

Sub Mark_Bill_as_Paid()
Dim Cell As Range
Dim RefCell As Range
  For Each Cell In Selection
    On Error Resume Next
    Set RefCell = Evaluate(Cell.Formula) 'how to handle if error
    On Error Resume Next
        If Len(Cell.Text) > 0 Then
           If Cell.Font.Strikethrough = True Then
              Cell.Font.ColorIndex = -4105 'automatic
              Cell.Font.Strikethrough = False
              RefCell.Font.ColorIndex = -4105 'automatic
              RefCell.Font.Strikethrough = False
           Else
              Cell.Font.ColorIndex = 5 'blue
              Cell.Font.Strikethrough = True
              RefCell.Font.ColorIndex = 5 'blue
              RefCell.Font.Strikethrough = True
           End If
        End If
  Next
End Sub

An improvement I am hoping someone can help me with is to make it so it is cycles through three options.

Initially, no strike-trough and automatic color,
First click, strike-through and blue color,
Second click, strike-through and red color,
Third click, return to initial no strike through and automatic color
Fourth click and on repeats (same as first click above)

Thanks to everyone who assists.