How to make a Column .Font.Bold = False, with one exception.

[Eloise T]

I need a line of code that makes Column H non-boldface Except any cell in Column H that begins with "NO INVOICE..." which is to remain BOLDFACE.
Thank you in advance.

' This VBA segment changes the currency in Column H from whatever the format happens to be to "$#,##0"
Sub CurrencyFix()
' Affects only <Column H> "Amount"
    Dim lRow As Long, iColumn As Integer, lRowest As Long
    Dim ws As Worksheet
' start 1Row starts at row 3, iColumn starts at column H(8) H is the 8th column alphabetically.
    lRow = 3
    iColumn = 8
' process
    With ws
        lRowest = .Cells(.Rows.Count, iColumn).End(xlUp).Row
        Do While lRow <= lRowest
            ' Debug.Print lRow
            With .Cells(lRow, iColumn)
                If .NumberFormat <> "$#,##0" Then .NumberFormat = "$#,##0"


' At some point here or close to here I need a line of code
' that makes Column H non-boldface Except any cell in
' Column H that begins with "NO INVOICE..." which is to
' remain BOLDFACE.
                     .Font.Bold = False
                 
                 
                 
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                    lRow = lRow + 1
            End With
        Loop
    End With
End Sub

 

[vletm]

Eloise T

FB = False
If Left(.value,13) = "NO INVOICE..." then FB = True
.Font.Bold = FB

 

[Eloise T]

Eloise T

FB = False
If Left(.value,13) = "NO INVOICE..." then FB = True
.Font.Bold = FB

The "NO INVOICE..." is in reality "NO INVOICE FOR WEEK ENDING 01/04/2019"

The line needs to accomodate:
"If Left(.value,37) = "NO INVOICE FOR WEEK ENDING mm/dd/yyyy"

Will this work?
"If Left(.value,10) = "NO INVOICE *"

I don't understand how accurate the number after the comma has to be in the parenthesis and if I can use a * to be a wildcard?

 

[vletm]

Eloise T
You few times wrote "NO INVOICE...", that's why I used same text!
Now, there aren't those "...", then You could use

if Left(.value,10) = "NO INVOICE" then FB = true​

IF that part text cannot mess with other same kind of sentences.
.. for me, if (try to) use a wildcard
... it (a wildcard) would be as any mark which should be there!
( as well as text mm/dd/yyyy .. should find as those marks! not 'date'! )

Ps. Other thing ...
This seems to be 'same sheet' which You've worked for a while ...
Your code checks cell-by-cell it ... and maybe many times.
Have You tried ...
if You in the beginning 'reset' the whole sheet?
'Reset' means, that code would set default settings to large range (than a cell).
Eg 'all cells .font.bold = false'
After that, the code would change ONLY needed cells.

 

[Eloise T]

I changed the code to the following believing/hoping the wild card "*" would work. It did not...

FB = False
If Left(.Value, 37) = "NO*" Then FB = True
.Font.Bold = FB

The lines "NO INVOICE FOR WEEK ENDING mm/dd/yyyy" are right aligned/justified and appear in Column H (last column on the right) and need to remain BOLD face. Currently, they are not.


Regarding your suggestion to do a reset and set the default to Bold.Font = False,
I plan to tackle that once I get the wild card to work.

 

[GraH - Guido]

Hmm, why not try with conditional format?

 

[vletm]

Eloise T
As I tried to explain...
a) if use * then there should be *
b) If Left(.Value, 37) = "NO*"...
now You compare 37 'marks' from .value to THREE marks! ... hmm?

c) As written: then You could use
if Left(.value,10) = "NO INVOICE" then FB = true
which compares 10 'marks' to "NO INVOICE" .. which could be true.

 

[Eloise T]

Eloise T
As I tried to explain...
a) if use * then there should be *
b) If Left(.Value, 37) = "NO*"...
now You compare 37 'marks' from .value to THREE marks! ... hmm?

c) As written: then You could use
if Left(.value,10) = "NO INVOICE" then FB = true
which compares 10 'marks' to "NO INVOICE" .. which could be true.

a) So * does not work as a wild card in VBA?
b) I used 37 as there would always be 37 characters. I thought I had to account for all 37 spaces...I see I do not and now I understand why that won't work.
c) if Left(.value,10) = "NO INVOICE" then FB = true WORKED! Thank you.

 

[Eloise T]

Hmm, why not try with conditional format?

I may try Conditional Format later when I have time to experiment.
Thank you for the suggestion.

 

[vletm]

Eloise T
a) * works even with VBA, if use it as it should use,
as well as with formulas test =if(left(a1,2) = "NO*","YES","MAYBE") ((a1 is as .value))
b) as well as there aren't text as mm/dd/yyyy
c) "NO INVOICE" = "NO INVOICE"
Gotta ask ... curiosity kills cat -- or not:
Do this 'sheet' also have over 25000 rows?
How many columns has same over 25'000 formulas or conditional formatting?
... as with that 'filtering' case ... instead of using VBA

 

[Eloise T]

Eloise T
a) * works even with VBA, if use it as it should use,
as well as with formulas test =if(left(a1,2) = "NO*","YES","MAYBE") ((a1 is as .value))
b) as well as there aren't text as mm/dd/yyyy
c) "NO INVOICE" = "NO INVOICE"
Gotta ask ... curiosity kills cat -- or not:
Do this 'sheet' also have over 25000 rows?
How many columns has same over 25'000 formulas or conditional formatting?
... as with that 'filtering' case ... instead of using VBA

Thank you.