Name Manager assistance.

[Eloise T]

In the "Sixth" tab of the workbook, in Column C, there are 9 cells (C3 through C11) where part of the characters are in Bold Red because those characters are listed in the Name Manager. Cells C12 through C14 are also listed in the Name Manager but the 75 is not Bold Red like I think it should be....Why not?

Special note to look at the REDnBOLD macro.

Please see attached file. Thanks for looking.

 

[p45cal]

Conditional formatting font colour and bold is overriding manual/vba colouring

 

[Eloise T]

Conditional formatting font colour and bold is overriding manual/vba colouring

If that's so, why are the others in Name Manager working?

 

[Hui]

Remove the CF then you'll see why

 

[p45cal]

Conditional formatting (call it CF) can be quirky, but I think we've got it solved.
When I open your most recent file (Office 365) I get this:

Note:
1. That there are no gradient fills
2. The bottomost cells with 75 have no red bolding, but cells above have. It seems maybe because they are the first characters, not mid-string characters.

To correct this select a cell in the range, go into the CF and Manage rules…

and double-click or edit the errant one (selected above). Actually, there are several errant ones.

Go into Format…


Go to the Font tab (note that Regular is selected as part of the CF and that the colour is Black; this is what is overriding manual/vba highlighting) and click Clear:


If Clear is greyed out, choose, say, Bold in the Font Style section, which should enable the button and press Clear Again. Click OK. OK again.
Click Apply.
I now get this:


Now I've not only got the red bolding you want, but also the gradient fill.
It may pay you to do something similar to the other CFs in that range.

 

[p45cal]

there are 9 cells (C3 through C11) where part of the characters are in Bold Red because those characters are listed in the Name Manager.

Just to point out that the Bold Red is not because of Name Manager entries, but solely down to the RednBold macro which highlights the first pair of digits found in the string between 70 and 90 inclusive.
The Name Manager entries are used in conditional formatting mainly to change the background in the cell when it finds more specific character sequences. In fact, its altering of the font characteristics was the cause of this present problem.

 

[Peter Bartholomew]

@p45cal
Nice piece of detective work and I liked the effort you had put into the presentation. To get the gradient fills, I had to start from scratch and redefine them though. I don't think I have come across conditional formatting that is quite as 'exuberant' before!

 

[Eloise T]

Thank you p45cal (outstanding) and Hui. The [Clear] immediately solved the issue. I don't why I didn't run across this problem before as I have added more entries to the Name Manager in the past without problems. I would have never figured out using the clear button would have solved the problem.

For some unknown reason (to me), the CFs have always given me problems (not just in this Excel file) as they want to "self-adjust" without my permission. My solution was to create 2 macros (Conditional_Format_Reset) and (REDnBOLD) to whip them back into shape.

 

[p45cal]

My solution was to create 2 macros (Conditional_Format_Reset) and (REDnBOLD) to whip them back into shape.

If you use Conditional_Format_Reset then you're likely to re-introduce the problem! In the code for the relevant CFs, you have several instances of lines like:

      .FormatConditions(.FormatConditions.Count).Font.ColorIndex = 1    'Black font
      .FormatConditions(.FormatConditions.Count).Font.Bold = False
      .FormatConditions(.FormatConditions.Count).Font.Italic = False

You need to remove them just for those SearchFor CFs. Remember, this is to ensure RednBold works when the numerals to be highlighted are at the beginnng of the string, so you won't see the effect of removing those lines of code until you have such numbers.