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!

 

[Rick Rothstein]

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

I assume by the above statement that you mean you have VBA code placing the values into Column H so that those values are constants and the blank cells are true blank cells. If that is the case, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub Format_GrandTotals()
  Dim Ar As Range
  For Each Ar In Range("H2", Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlBlanks).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]

WOW...IT WORKS PERFECTLY !!

Thanks so much for helping me Rick...especially at this late hour.

With much gratitude,

Jerry

 

[Rick Rothstein]

WOW...IT WORKS PERFECTLY !!

Thanks so much for helping me Rick...especially at this late hour.

Just so you know, the code Peter posted in Message #3 can also be made to work for you. If you notice, Peter posted that he assumed your values in Column H were formulas and he structured his code accordingly. Since that is not the case, one small change to his code (I highlighted it in red) makes it work with your constants (non-formulas) as well...

[table="width: 500"]
[tr]
	[td]Sub Format_Totals_v1()
  Dim rA As Range
  
  For Each rA In Range("H2", Range("H" & Rows.Count).End(xlUp)).SpecialCells([B][COLOR="#FF0000"]xlConstants[/COLOR][/B], 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[/td]
[/tr]
[/table]

 

[afterdinnerspeaker]

Thanks for the follow-up Rick. That makes sense...I'm so glad you asked me the question about the values being "constants". Cheers, Jerry

 

[afterdinnerspeaker]

Just so you know, the code Peter posted in Message #3 can also be made to work for you. If you notice, Peter posted that he assumed your values in Column H were formulas and he structured his code accordingly. Since that is not the case, one small change to his code (I highlighted it in red) makes it work with your constants (non-formulas) as well...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Format_Totals_v1()
  Dim rA As Range
  
  For Each rA In Range("H2", Range("H" & Rows.Count).End(xlUp)).SpecialCells([B][COLOR=#FF0000]xlConstants[/COLOR][/B], 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[/TD]
[/TR]
</tbody>[/TABLE]

Hi again Rick!

So sorry to bother you again but wondering if you could make a slight adjustment to the code you wrote for me almost 2 months ago?

I just started using your code on my income tax spreadsheets and noticed the last figure in column "H" is not bolded or highlighted. Everything else works perfectly but I hadn't noticed this little glitch until a couple of days ago.

I can send you an actual copy of the spreadsheet if necessary.

I tried altering the code myself but had to give up.

Thanks so much,

Jerry

 

[Rick Rothstein]

Hi again Rick!

I just started using your code on my income tax spreadsheets and noticed the last figure in column "H" is not bolded or highlighted. Everything else works perfectly but I hadn't noticed this little glitch until a couple of days ago.

Is the last figure in Column H a constant or a formula?

 

[afterdinnerspeaker]

Is the last figure in Column H a constant or a formula?

Thanks Rick,

The last figure is a constant as are the others in the row.

Jerry

 

[afterdinnerspeaker]

Hi again Rick!

After lots of experimenting, I was able to copy & paste a portion of the spreadsheets to show you (except I can't show you the yellow highlighting where text is red).

In the first example, running the macro performs perfectly, except it changes the "Balance" heading from Bold to Normal.

Source #	Debits	Credits	Balance
			0.00
			0.00
Burns Valkenburg	400.00	0.00	400.00
			0.00
AMA	178.00	0.00	178.00
			0.00
			0.00
Special Areas	2,023.59	0.00	2,023.59
			0.00
UFA	129.95	0.00	129.95
			0.00
Special Areas	4,071.40	0.00	4,071.40
			0.00
Armtec Calgary	99.44	0.00	99.44
Tasmanian Septic	140.00	0.00	4,327.53
	239.44	0.00
			0.00
Telus Communications	13.83	0.00	13.83
Telus Communications	13.83	0.00	151.66
	27.66	0.00
			0.00
			0.00

<tbody>
</tbody>

In the second example, running the same macro reformats everything except the last cell in the column.

As before, it changes the "Balance" heading from Bold to Normal.

Source #	Debits	Credits	Balance
			0.00
			0.00
BVA	300.00	0.00	300.00
			0.00
AMA	96.00	0.00	96.00
AMA	96.00	0.00	192.00
AMA	89.00	0.00	281.00
	281.00	0.00
			0.00
Special Areas	2,298.24	0.00	2,298.24
			0.00
Special Areas	3,996.35	0.00	3,996.35
			0.00
Soper's Supply	146.52	0.00	146.52
Frontier Waterworks	33.67	0.00	8,700.67
	180.19	0.00
			0.00
Telus Communications	13.83	0.00	13.83
Telus Communications	13.83	0.00	165.96
	27.66	0.00
			0.00
Oyen Vet. Services	236.00	0.00	236.00
			0.00
Meyer Drilling Ltd.	30,000.00	0.00	30,000.00

<tbody>
</tbody>

I wish there was some way of transmitting the actual section of the spreadsheet so you could replicate what's happening.

It's such a strange thing and doesn't make any sense at all.

I could easily make those minor changes manually but I'm sure you'd like to solve this mystery as well.

Many thanks,

Jerry

 

[afterdinnerspeaker]

Good Morning Rick,

Just wondering if you had any more thoughts about how to adjust the code to eliminate the problem?

Also, is there any other way I could send you the actual spreadsheet?

Thanks Rick,

Jerry

 

[afterdinnerspeaker]

Just so you know, the code Peter posted in Message #3 can also be made to work for you. If you notice, Peter posted that he assumed your values in Column H were formulas and he structured his code accordingly. Since that is not the case, one small change to his code (I highlighted it in red) makes it work with your constants (non-formulas) as well...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Format_Totals_v1()
  Dim rA As Range
  
  For Each rA In Range("H2", Range("H" & Rows.Count).End(xlUp)).SpecialCells([B][COLOR=#FF0000]xlConstants[/COLOR][/B], 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[/TD]
[/TR]
</tbody>[/TABLE]

Hi Peter!

I don't know if you received my last posts but in the meantime I was able to solve the problem.

I went back to the code that Peter wrote with the change you suggested (in red) and it works right to the end of column "H".It also leaves the "Balance" heading Bolded.

I just thought I'd share that with you and also thank you again for all your help.

Regards,

Jerry