VBA that detects conditional formatting

jupasto

New Member
Joined
Aug 19, 2019
Messages
9
Hello,

To preface I'm a total noob with VBA but I found a simple VBA function on the web that tests if a cell is bold or not and returns true or false. I also have some conditional formatting set up to turn text bold if it's within a certain date.

My problem is that even though the conditional formatting works and turns the text bold, the "ISBOLD" function still returns false. Is there a way to modify the VBA to detect the bold text?











Code:
Function ISBOLD(cell As Range) As Boolean

  Application.Volatile True
  On Error GoTo PartiallyBold
    ISBOLD = cell.Characters.Font.Bold
  On Error GoTo 0

Exit Function

PartiallyBold:
  If Err.Number = 94 Then ISBOLD = True

End Function
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Re: Help with VBA that detects conditional formatting

Hi & welcome to MrExcel.
Assuming that you are calling that function from the worksheet, then I'm afraid that it can't done.
 
Upvote 0
Re: Help with VBA that detects conditional formatting

You're welcome & thanks for the feedback.
If it's of any help a macro can detect CF formats, but it wouldn't be automatic.
 
Upvote 0
Re: Help with VBA that detects conditional formatting

Hmm it sounds like you're saying there's is a way to use a VBA macro/function to detect conditionally formatted bold text, but it wouldn't be volatile i.e. update automatically?

I'm OK with hitting F9 or whatever to refresh the function if I could get it to return true when the conditional formatting is on...
 
Upvote 0
Re: Help with VBA that detects conditional formatting

It would need to be a macro, which is different from a UDF (user defined function).
What ranges would you want to check & where would you want the answer?
 
Upvote 0
Re: Help with VBA that detects conditional formatting

Right now I've got the conditional formatting set up to check cell A5 for the number of service years. Then it bolds one of the cells between D6 & D11 depending on the range. D6 = 1 - 4 service years, D7 = 5 - 9 service years, D8 = 10 - 14, D9 = 15 - 19, D10 = 20 - 24; D11 = 25 - 50.

I had cell A12 with the ISBOLD function output.
 
Upvote 0
Re: Help with VBA that detects conditional formatting

Instead of creating a UDF or macro, why not just put the same formula you use for CF in the A12 cell?
 
Upvote 0
Re: Help with VBA that detects conditional formatting

Yeah you're right, I don't really need that UDF, but since I had it included I was just curious if there was a way to make it work. Just for my own personal edification and in case I had a use for something like it in the future, that's all.
 
Upvote 0
Re: Help with VBA that detects conditional formatting

It is theoretically possible, but probably not worth the effort. I was playing around with this UDF:

Code:
Function ISBOLD(cell As Range) As Boolean


    Application.Volatile
    ISBOLD = cell.Font.Bold
    For Each x In cell.FormatConditions
        If x.Font.Bold Then
            Debug.Print Range(x.AppliesTo.Address).Resize(1, 1).Address
            If Evaluate(x.Formula1) Then ISBOLD = True
        End If
    Next x
End Function
It goes through any conditional formulas for your range, sees which ones will make the font Bold, then gets the formula used, evaluates it, and if it's true, then sets the ISBOLD value to true. It does NOT work as is. The biggest issue is that the formula you get is the formula which is based on the first cell in the range. You can get the range from the AppliesTo.Address, then the upper left cell (see the Debug.Print line), but then you have to change any relative references in the formula. And if that's not bad enough, you have to worry about stop conditions from other rules.

So if you really wanted to do it, you could, but you'd probably need a pretty big reason to do so.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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