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

A

afterdinnerspeaker

Board Regular
Joined
Jan 10, 2019
Messages
70
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!
 
Sort by date Sort by votes
afterdinnerspeaker said:
No, the numbers in column H are strictly the result of formulas in adjacent columns.
Click to expand...
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...
Code: 
[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]
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
afterdinnerspeaker said:
WOW...IT WORKS PERFECTLY !!

Thanks so much for helping me Rick...especially at this late hour.
Click to expand...
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...
Code: 
[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]
 
Upvote 0
Rick Rothstein said:
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...
Code: 
[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]
Click to expand...

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
 
Last edited:
Upvote 0
afterdinnerspeaker said:
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.
Click to expand...

Is the last figure in Column H a constant or a formula?
 
Upvote 0
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.


[TABLE="width: 287"]
<tbody>[TR]
[TD]Source #[/TD]
[TD]Debits[/TD]
[TD]Credits[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]Burns Valkenburg[/TD]
[TD]400.00[/TD]
[TD]0.00[/TD]
[TD]400.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]AMA[/TD]
[TD]178.00[/TD]
[TD]0.00[/TD]
[TD]178.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]Special Areas[/TD]
[TD]2,023.59[/TD]
[TD]0.00[/TD]
[TD]2,023.59[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]UFA[/TD]
[TD]129.95[/TD]
[TD]0.00[/TD]
[TD]129.95[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]Special Areas[/TD]
[TD]4,071.40[/TD]
[TD]0.00[/TD]
[TD]4,071.40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]Armtec Calgary[/TD]
[TD]99.44[/TD]
[TD]0.00[/TD]
[TD]99.44[/TD]
[/TR]
[TR]
[TD]Tasmanian Septic[/TD]
[TD]140.00[/TD]
[TD]0.00[/TD]
[TD]4,327.53[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]239.44[/TD]
[TD]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]Telus Communications[/TD]
[TD]13.83[/TD]
[TD]0.00[/TD]
[TD]13.83[/TD]
[/TR]
[TR]
[TD]Telus Communications[/TD]
[TD]13.83[/TD]
[TD]0.00[/TD]
[TD]151.66[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]27.66[/TD]
[TD]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
</tbody>[/TABLE]


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.


[TABLE="width: 297"]
<tbody>[TR]
[TD]Source #[/TD]
[TD]Debits[/TD]
[TD]Credits[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00 [/TD]
[/TR]
[TR]
[TD]BVA[/TD]
[TD]300.00 [/TD]
[TD]0.00 [/TD]
[TD]300.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00 [/TD]
[/TR]
[TR]
[TD]AMA[/TD]
[TD]96.00 [/TD]
[TD]0.00 [/TD]
[TD]96.00 [/TD]
[/TR]
[TR]
[TD]AMA[/TD]
[TD]96.00 [/TD]
[TD]0.00 [/TD]
[TD]192.00 [/TD]
[/TR]
[TR]
[TD]AMA[/TD]
[TD]89.00 [/TD]
[TD]0.00 [/TD]
[TD]281.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]281.00 [/TD]
[TD]0.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00 [/TD]
[/TR]
[TR]
[TD]Special Areas[/TD]
[TD]2,298.24 [/TD]
[TD]0.00 [/TD]
[TD]2,298.24[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00 [/TD]
[/TR]
[TR]
[TD]Special Areas[/TD]
[TD]3,996.35 [/TD]
[TD]0.00 [/TD]
[TD]3,996.35[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00 [/TD]
[/TR]
[TR]
[TD]Soper's Supply[/TD]
[TD]146.52 [/TD]
[TD]0.00 [/TD]
[TD]146.52 [/TD]
[/TR]
[TR]
[TD]Frontier Waterworks[/TD]
[TD]33.67 [/TD]
[TD]0.00 [/TD]
[TD]8,700.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]180.19 [/TD]
[TD]0.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00 [/TD]
[/TR]
[TR]
[TD]Telus Communications[/TD]
[TD]13.83 [/TD]
[TD]0.00 [/TD]
[TD]13.83 [/TD]
[/TR]
[TR]
[TD]Telus Communications[/TD]
[TD]13.83 [/TD]
[TD]0.00 [/TD]
[TD]165.96[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]27.66 [/TD]
[TD]0.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00 [/TD]
[/TR]
[TR]
[TD]Oyen Vet. Services[/TD]
[TD]236.00 [/TD]
[TD]0.00 [/TD]
[TD]236.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00 [/TD]
[/TR]
[TR]
[TD]Meyer Drilling Ltd.[/TD]
[TD]30,000.00 [/TD]
[TD]0.00 [/TD]
[TD]30,000.00 [/TD]
[/TR]
</tbody>[/TABLE]


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
 
Upvote 0
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
 
Upvote 0
Rick Rothstein said:
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...
Code: 
[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]
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
207
Omran Y
O
A
  • Solved
exclude zero, populate last amount for adjacent last enter cell for each range
Replies
6
Views
126
Alaa mg
A
A
  • Solved
Match three columns together across sheet to subtraction based invoice number
2
Replies
10
Views
225
abdo meghari
A
A
  • Question Question
Daily Interest Loan Scheduler | Missed Payments
Replies
0
Views
241
aurelius33
A
E
  • Solved
Help speed up "change font color" routine
Replies
5
Views
251
EssKayKay
E

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top