If *Total* make row white
 

I grabbed this from another macro that I have:
Dim lastrow As Long, j As Long
lastrow = Cells(Rows.Count, 5).End(xlUp).Row
For j = lastrow To 1 Step -1
If (Cells(j, 5).Value) Like "*Total*" Then
Rows(j).Interior.ColorIndex = xlNone
End If
Next

Other macro worked great, but the way. I tried to white out each row, and
now it doesn't work. Been looking at all kinds of code for about 10 hours
now. My eyes need a rest. Can someone see the problem?

Thanks!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

If *Total* make row white
 

Well, I just found the culprit; I have this in two cells (one of each):
#VALUE!
#VALUE! Total

How can I compensate for this?

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"marcus" wrote:

Hi Ryan

This works fine on my machine. It turns the rows in Column E back to
the normal uncoloured state. If you want it to be white like a ghost
try something like this.

Take care

Marcus

Option Explicit
Option Compare Text

Sub TotalColour()
Dim lastrow As Long, j As Long
lastrow = Cells(Rows.Count, 5).End(xlUp).Row
For j = lastrow To 1 Step -1
If (Cells(j, 5).Value) Like "*Total*" Then
Rows(j).Interior.ColorIndex = 2
End If
Next

End Sub
.

If *Total* make row white
 

I got it working with this:

Dim j As Long
For j = Cells(Rows.Count, "E").End(xlUp).Row To 1 Step -1
If InStr(1, Cells(j, 5).Text, "Total", vbTextCompare) < 0 Then
Rows(j).Interior.ColorIndex = xlNone
Rows(j + 1).Interior.ColorIndex = xlNone
End If
Next

The above code is from Jacob Skaria! Genius!! I swear I tried that earlier
in the evening and it didn't work. It was a long day...

Other solutions work, but it seems like Jacob's solution is the only one
that worked with those dang #VALUE! things in there.


Thanks all!
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"J_Knowles" wrote:

If *Total* in Column E, make row font white, Maybe this is what your after:

Sub FontColorForTotal()
Dim lastrow As Long, j As Long
On Error Resume Next
lastrow = Cells(Rows.Count, 5).End(xlUp).Row
For j = lastrow To 1 Step -1
If LCase(Cells(j, 5).Value) Like LCase("*Total*") Then
Rows(j).Font.ColorIndex = 2 'white
Else
Rows(j).Font.ColorIndex = 1 'black
End If
Next
End Sub
HTH
--
Data Hog


"ryguy7272" wrote:

I grabbed this from another macro that I have:
Dim lastrow As Long, j As Long
lastrow = Cells(Rows.Count, 5).End(xlUp).Row
For j = lastrow To 1 Step -1
If (Cells(j, 5).Value) Like "*Total*" Then
Rows(j).Interior.ColorIndex = xlNone
End If
Next

Other macro worked great, but the way. I tried to white out each row, and
now it doesn't work. Been looking at all kinds of code for about 10 hours
now. My eyes need a rest. Can someone see the problem?

Thanks!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.