Macro to bold, highlight & change font color of certain cells in a column

[afterdinnerspeaker]

MACRO TO BOLD, HIGHLIGHT & CHANGE FONT COLOR OF CERTAIN CELLS IN A COLUMN


I’m embarrassed to say how long I’ve been searching for an answer to the following problem:

The last column(“H”) on my spreadsheet contains the numeric totals of expenses in each row.
There are also spaces between some rows (blank cells).

Cells in that column are either zeros (0.00) or contain a number greater than zero or they’re blank.

My purpose is to find these specific totals to BOLD, highlight & change font color.


COLUMN “H”

Balance

0.00

0.00
6,638.84
15,250.15
43,193.79 ß--------


0.00
4,800.00 ß--------

0.00

0.00

0.00

0.00


0.00


Many thanks in advance for any help you can give me!

 

[afterdinnerspeaker]

Sorry, had to re-do post!

 

[afterdinnerspeaker]

Welcome to the MrExcel board!

Try this in a copy of your workbook. It assumes that those values in column H are the results of formulas.

Sub Format_Totals_v1()
  Dim rA As Range
  
  For Each rA In Range("H2", Range("H" & Rows.Count).End(xlUp)).SpecialCells(xlFormulas, xlNumbers).Areas
    With rA.Cells(rA.Cells.Count)
      If .Value > 0 Then
        With .Font
          .Bold = True
          .Color = vbRed
        End With
        .Interior.Color = vbYellow
      End If
    End With
  Next rA
End Sub

Hi Peter!

I just wanted to say thank you again for your help almost 2 months ago.

Initially, with your code, I was getting the dreaded "Run-time error '1004' No cells were found".

In the meantime, Rick suggested his own version which worked well at the beginning but had a tiny glitch which didn't show up until recently.

However, Rick had suggested earlier that your macro, with a slight change (xlFormulas to xlConstants) would work as well.

Just today, I returned to your code (with that change made) and it works like a dream without the glitches!

You both offer such a terrific service and I'm very grateful.

Cheers,

Jerry

 

[afterdinnerspeaker]

Welcome to the MrExcel board!

Try this in a copy of your workbook. It assumes that those values in column H are the results of formulas.

Sub Format_Totals_v1()
  Dim rA As Range
  
  For Each rA In Range("H2", Range("H" & Rows.Count).End(xlUp)).SpecialCells(xlFormulas, xlNumbers).Areas
    With rA.Cells(rA.Cells.Count)
      If .Value > 0 Then
        With .Font
          .Bold = True
          .Color = vbRed
        End With
        .Interior.Color = vbYellow
      End If
    End With
  Next rA
End Sub

Hi Peter!

As per suggestion from Rick a while back, I simply changed SpecialCells(xlFormulas, xlNumbers) to SpecialCells(xlConstants, xlNumbers) to make your code work when values in column H are constants.

Just wondering if there's any way to alter the code if column H has a mix of both "results of formulas" (as in your original code) and "constants"?

Many thanks,

Jerry