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 to have missed mentioning the search criteria:

* cells must be greater than zero
* cells must be followed by at least one blank space

OBJECT IS TO REFORMAT ONLY GRAND TOTALS

 

[Peter_SSs]

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

 

[afterdinnerspeaker]

Thank you so much Peter. I will try it right now!

 

[afterdinnerspeaker]

Hi Peter, I'm probably doing something wrong as I get the error message: Run-time error '1004' No cells were found.

 

[afterdinnerspeaker]

Could it be because there are text headings like "balance" at top of column ("H")?

 

[Rick Rothstein]

Does this macro work for you...

[table="width: 500"]
[tr]
	[td]Sub Format_GrandTotals()
  Dim Ar As Range
  For Each Ar In Range("F2", Cells(Rows.Count, "F").End(xlUp)).SpecialCells(xlFormulas, xlTextValues).Areas
    With Ar(1).Offset(-1)
      .Font.Bold = Val(.Value) > 0
      If .Font.Bold Then
        .Font.Color = vbRed
        .Interior.Color = vbYellow
      End If
    End With
  Next
End Sub[/td]
[/tr]
[/table]

 

[afterdinnerspeaker]

Thanks Rick! Unfortunately, I still get the same run-time error '1004' (No cells were found).

The column is actually "H" so I changed the "F" to "H" but still got the error. My column "H" is no more than 300 rows long (not sure if that makes any difference).

Many thanks, Jerry

 

[Rick Rothstein]

Thanks Rick! Unfortunately, I still get the same run-time error '1004' (No cells were found).

The column is actually "H" so I changed the "F" to "H" but still got the error. My column "H" is no more than 300 rows long (not sure if that makes any difference).

Do you have formulas in Column H ?

 

[afterdinnerspeaker]

No, the numbers in column H are strictly the result of formulas in adjacent columns.