Determine actual formula being used for a specific cell in a complex conditional fomatting formula

[Steve Bayliss]

Hi All,

This has bugged me for a while,

When I have complex conditional formatting formulas using and's or's iserror's vlookup's etc.. , I sometimes wants to troubleshoot the formula based on a specific cell in the range that the formatting applies to rather than seeing the formula for the first cell of the range.

So to simply the question... if I create a conditional format for the range "C5:F20" and the formula is =C$2>=$A5 then I want to be able to select cell E10 and see that the formula actually evaluated for that cell =E$2>=$A10 instead of seeing =C$2>=$A5 which of course represents the formula for the first cell in the range that the formatting is being applied to.

Thanks

Steve

 

[RoryA]

Unfortunately, from 2007 onward there is no really easy way to do that. Even VBA will report the cell's formatcondition.formula1 as it is shown in the CF dialog, so you need to jump through some hoops. You can use something like this:

Sub GetRelativeFC()
    Dim fc                    As FormatCondition
    Dim sFormula              As String
    Dim sCF                   As String
    On Error Resume Next
    For Each fc In ActiveCell.FormatConditions
        sCF = Application.ConvertFormula(Formula:=fc.Formula1, fromreferencestyle:=xlA1, _
                            toreferencestyle:=xlR1C1, relativeto:=fc.AppliesTo.Cells(1))
        sFormula = sFormula & vbLf & Application.ConvertFormula(Formula:=sCF, fromreferencestyle:=xlR1C1, _
                            toreferencestyle:=xlA1, relativeto:=ActiveCell)
    Next fc
    MsgBox Mid$(sFormula, 2)
End Sub

 

[Steve Bayliss]

Thanks Rory,

Good to know I wasn't missing how to easily do it.

Thanks for that.

Might change the MsgBox to a Debug.Print so I can run that and copy paste the formula from the intermediate window into a blank cell to check it further.

Cheers

Steve