CONDITIONAL FORMATTING IN VB
 

I had a wonderful person from this forum help design this VB code a few
months ago.. but I thought we were also bolding the row and that is not
happening.. I don't know if I inadvertently deleted the code row that did
that or what.. in any event, the macro works perfectly except that the amount
does not bold..

What I am doing is running the subtotal function from Excel and then run the
macro to highlight AND bold THE ENTIRE ROW if that ROW has the word total in
it.. this helps bring out the sub totals and grand totals.. Also, we do not
want the first row (column heading) to bold or highlight even though it has
the word total in it...

Here is the macro.. if someone could help me add the line (or lines) of code
to get the data to ALSO bold, and to NOT have the header row bold or
highlight, that would be great... I tried a few things on my own, but just
don't have the knowledge...
Thank you in advance..

Sub FormatTotalPerfect()
Dim Tarea As Range
Dim lr As Long, lc As Long

Cells.Interior.ColorIndex = xlNone

lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))

For Each rng In Tarea.Rows
If Application.CountIf(rng, "*TOTAL*") 0 Then
Range(Cells(rng.Row, "A"), Cells(rng.Row, lc)) _
.Interior.ColorIndex = 36
End If
Next

End Sub

CONDITIONAL FORMATTING IN VB
 

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))
starts at A1

Try:
Set Tarea = Range(Cells(2, "A"), Cells(lr, lc))
or
Set Tarea = Range("A2", Cells(lr, lc))

And add this line:
.font.bold = true
after:
.Interior.ColorIndex = 36



Tree wrote:

I had a wonderful person from this forum help design this VB code a few
months ago.. but I thought we were also bolding the row and that is not
happening.. I don't know if I inadvertently deleted the code row that did
that or what.. in any event, the macro works perfectly except that the amount
does not bold..

What I am doing is running the subtotal function from Excel and then run the
macro to highlight AND bold THE ENTIRE ROW if that ROW has the word total in
it.. this helps bring out the sub totals and grand totals.. Also, we do not
want the first row (column heading) to bold or highlight even though it has
the word total in it...

Here is the macro.. if someone could help me add the line (or lines) of code
to get the data to ALSO bold, and to NOT have the header row bold or
highlight, that would be great... I tried a few things on my own, but just
don't have the knowledge...
Thank you in advance..

Sub FormatTotalPerfect()
Dim Tarea As Range
Dim lr As Long, lc As Long

Cells.Interior.ColorIndex = xlNone

lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))

For Each rng In Tarea.Rows
If Application.CountIf(rng, "*TOTAL*") 0 Then
Range(Cells(rng.Row, "A"), Cells(rng.Row, lc)) _
.Interior.ColorIndex = 36
End If
Next

End Sub

--

Dave Peterson

CONDITIONAL FORMATTING IN VB
 

Thank you Dave for your help! I did put in:
..font.bold = true after the line
.Interior.ColorIndex = 36 before I wrote the post as well, thinking
that would add the bolding, however, when I do that, I get the following
error message:

COMPILE ERROR
INVALID OR UNQUALIFIED REFERENCE

and when I look that up in Help, it says something about missing something
for the With Statement..

Again, your assistance is very much appreciated!!
Thank you for your time!

"Dave Peterson" wrote:

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))
starts at A1

Try:
Set Tarea = Range(Cells(2, "A"), Cells(lr, lc))
or
Set Tarea = Range("A2", Cells(lr, lc))

And add this line:
.font.bold = true
after:
.Interior.ColorIndex = 36



Tree wrote:

I had a wonderful person from this forum help design this VB code a few
months ago.. but I thought we were also bolding the row and that is not
happening.. I don't know if I inadvertently deleted the code row that did
that or what.. in any event, the macro works perfectly except that the amount
does not bold..

What I am doing is running the subtotal function from Excel and then run the
macro to highlight AND bold THE ENTIRE ROW if that ROW has the word total in
it.. this helps bring out the sub totals and grand totals.. Also, we do not
want the first row (column heading) to bold or highlight even though it has
the word total in it...

Here is the macro.. if someone could help me add the line (or lines) of code
to get the data to ALSO bold, and to NOT have the header row bold or
highlight, that would be great... I tried a few things on my own, but just
don't have the knowledge...
Thank you in advance..

Sub FormatTotalPerfect()
Dim Tarea As Range
Dim lr As Long, lc As Long

Cells.Interior.ColorIndex = xlNone

lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))

For Each rng In Tarea.Rows
If Application.CountIf(rng, "*TOTAL*") 0 Then
Range(Cells(rng.Row, "A"), Cells(rng.Row, lc)) _
.Interior.ColorIndex = 36
End If
Next

End Sub

--

Dave Peterson
.

CONDITIONAL FORMATTING IN VB
 

Sorry, I didn't read your original post close enough:

If Application.CountIf(rng, "*TOTAL*") 0 Then
with Range(Cells(rng.Row, "A"), Cells(rng.Row, lc))
.Interior.ColorIndex = 36
.font.bold = true
end with
End If

I didn't notice that continuation character (space underscore) in your original
code. I removed it and made it a with/end with structure. So both those lines
refer to the object (that range) in the previous With statement.



Tree wrote:

Thank you Dave for your help! I did put in:
.font.bold = true after the line
.Interior.ColorIndex = 36 before I wrote the post as well, thinking
that would add the bolding, however, when I do that, I get the following
error message:

COMPILE ERROR
INVALID OR UNQUALIFIED REFERENCE

and when I look that up in Help, it says something about missing something
for the With Statement..

Again, your assistance is very much appreciated!!
Thank you for your time!

"Dave Peterson" wrote:

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))
starts at A1

Try:
Set Tarea = Range(Cells(2, "A"), Cells(lr, lc))
or
Set Tarea = Range("A2", Cells(lr, lc))

And add this line:
.font.bold = true
after:
.Interior.ColorIndex = 36



Tree wrote:

I had a wonderful person from this forum help design this VB code a few
months ago.. but I thought we were also bolding the row and that is not
happening.. I don't know if I inadvertently deleted the code row that did
that or what.. in any event, the macro works perfectly except that the amount
does not bold..

What I am doing is running the subtotal function from Excel and then run the
macro to highlight AND bold THE ENTIRE ROW if that ROW has the word total in
it.. this helps bring out the sub totals and grand totals.. Also, we do not
want the first row (column heading) to bold or highlight even though it has
the word total in it...

Here is the macro.. if someone could help me add the line (or lines) of code
to get the data to ALSO bold, and to NOT have the header row bold or
highlight, that would be great... I tried a few things on my own, but just
don't have the knowledge...
Thank you in advance..

Sub FormatTotalPerfect()
Dim Tarea As Range
Dim lr As Long, lc As Long

Cells.Interior.ColorIndex = xlNone

lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))

For Each rng In Tarea.Rows
If Application.CountIf(rng, "*TOTAL*") 0 Then
Range(Cells(rng.Row, "A"), Cells(rng.Row, lc)) _
.Interior.ColorIndex = 36
End If
Next

End Sub

--

Dave Peterson
.


--

Dave Peterson

CONDITIONAL FORMATTING IN VB
 

YOU ARE SO AWESOME! PERFECT, PERFECT, PERFECT...
AND I really appreciate your explaining why the code was NOT working
before.. that underscore... THANK YOU!


"Dave Peterson" wrote:

Sorry, I didn't read your original post close enough:

If Application.CountIf(rng, "*TOTAL*") 0 Then
with Range(Cells(rng.Row, "A"), Cells(rng.Row, lc))
.Interior.ColorIndex = 36
.font.bold = true
end with
End If

I didn't notice that continuation character (space underscore) in your original
code. I removed it and made it a with/end with structure. So both those lines
refer to the object (that range) in the previous With statement.



Tree wrote:

Thank you Dave for your help! I did put in:
.font.bold = true after the line
.Interior.ColorIndex = 36 before I wrote the post as well, thinking
that would add the bolding, however, when I do that, I get the following
error message:

COMPILE ERROR
INVALID OR UNQUALIFIED REFERENCE

and when I look that up in Help, it says something about missing something
for the With Statement..

Again, your assistance is very much appreciated!!
Thank you for your time!

"Dave Peterson" wrote:

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))
starts at A1

Try:
Set Tarea = Range(Cells(2, "A"), Cells(lr, lc))
or
Set Tarea = Range("A2", Cells(lr, lc))

And add this line:
.font.bold = true
after:
.Interior.ColorIndex = 36



Tree wrote:

I had a wonderful person from this forum help design this VB code a few
months ago.. but I thought we were also bolding the row and that is not
happening.. I don't know if I inadvertently deleted the code row that did
that or what.. in any event, the macro works perfectly except that the amount
does not bold..

What I am doing is running the subtotal function from Excel and then run the
macro to highlight AND bold THE ENTIRE ROW if that ROW has the word total in
it.. this helps bring out the sub totals and grand totals.. Also, we do not
want the first row (column heading) to bold or highlight even though it has
the word total in it...

Here is the macro.. if someone could help me add the line (or lines) of code
to get the data to ALSO bold, and to NOT have the header row bold or
highlight, that would be great... I tried a few things on my own, but just
don't have the knowledge...
Thank you in advance..

Sub FormatTotalPerfect()
Dim Tarea As Range
Dim lr As Long, lc As Long

Cells.Interior.ColorIndex = xlNone

lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))

For Each rng In Tarea.Rows
If Application.CountIf(rng, "*TOTAL*") 0 Then
Range(Cells(rng.Row, "A"), Cells(rng.Row, lc)) _
.Interior.ColorIndex = 36
End If
Next

End Sub

--

Dave Peterson
.


--

Dave Peterson
.

CONDITIONAL FORMATTING IN VB
 

It's not really the continuation character that was the cause--it was my poor
eyesight!

You could have used:

Range(Cells(rng.Row, "A"), Cells(rng.Row, lc)) _
.Interior.ColorIndex = 36
Range(Cells(rng.Row, "A"), Cells(rng.Row, lc)) _
.font.bold = true

But why do all that typing <vbg???


Tree wrote:

YOU ARE SO AWESOME! PERFECT, PERFECT, PERFECT...
AND I really appreciate your explaining why the code was NOT working
before.. that underscore... THANK YOU!

"Dave Peterson" wrote:

Sorry, I didn't read your original post close enough:

If Application.CountIf(rng, "*TOTAL*") 0 Then
with Range(Cells(rng.Row, "A"), Cells(rng.Row, lc))
.Interior.ColorIndex = 36
.font.bold = true
end with
End If

I didn't notice that continuation character (space underscore) in your original
code. I removed it and made it a with/end with structure. So both those lines
refer to the object (that range) in the previous With statement.



Tree wrote:

Thank you Dave for your help! I did put in:
.font.bold = true after the line
.Interior.ColorIndex = 36 before I wrote the post as well, thinking
that would add the bolding, however, when I do that, I get the following
error message:

COMPILE ERROR
INVALID OR UNQUALIFIED REFERENCE

and when I look that up in Help, it says something about missing something
for the With Statement..

Again, your assistance is very much appreciated!!
Thank you for your time!

"Dave Peterson" wrote:

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))
starts at A1

Try:
Set Tarea = Range(Cells(2, "A"), Cells(lr, lc))
or
Set Tarea = Range("A2", Cells(lr, lc))

And add this line:
.font.bold = true
after:
.Interior.ColorIndex = 36



Tree wrote:

I had a wonderful person from this forum help design this VB code a few
months ago.. but I thought we were also bolding the row and that is not
happening.. I don't know if I inadvertently deleted the code row that did
that or what.. in any event, the macro works perfectly except that the amount
does not bold..

What I am doing is running the subtotal function from Excel and then run the
macro to highlight AND bold THE ENTIRE ROW if that ROW has the word total in
it.. this helps bring out the sub totals and grand totals.. Also, we do not
want the first row (column heading) to bold or highlight even though it has
the word total in it...

Here is the macro.. if someone could help me add the line (or lines) of code
to get the data to ALSO bold, and to NOT have the header row bold or
highlight, that would be great... I tried a few things on my own, but just
don't have the knowledge...
Thank you in advance..

Sub FormatTotalPerfect()
Dim Tarea As Range
Dim lr As Long, lc As Long

Cells.Interior.ColorIndex = xlNone

lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))

For Each rng In Tarea.Rows
If Application.CountIf(rng, "*TOTAL*") 0 Then
Range(Cells(rng.Row, "A"), Cells(rng.Row, lc)) _
.Interior.ColorIndex = 36
End If
Next

End Sub

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson