Filterable alternating row colour formula skewed when rows deleted.

[StargateFan]

I haven't tested this manually, actually, but the situation I have now
is this:

1. A3 has this formula so that the rows have alternating colour that
hold the alternating pattern even when filtered:

=AND(MOD(SUBTOTAL(3,$B3:$B$3),2)=0,$B3<>"")

2. The problem lies in that the sheet is protected and because of
this, I delete cells via a delete rows macro, which is this:

Sub DeleteRow()
Dim MyMsgBox As Long
MyMsgBox = MsgBox("Are you sure you really want to delete
this/these row(s)?? D", vbOKCancel + vbExclamation, "Delete ... ?")

If MyMsgBox = vbOK Then

Selection.EntireRow.Delete
ActiveCell.Select
ActiveCell.Offset(-1, 0).Select

End If
' Re-protects sheet in case anything unprotects it, yet allows vb
functioning to remain.
With ActiveSheet
.EnableAutoFilter = True
.Protect UserInterfaceOnly:=True
End With
End Sub

[** Re the above codes, none of them are mine. They were kind gifts
from people in this ng.]

3. The challenge is that today I ran into a "glitch" in that rows
deleted with the above delete row macro make the alternate colouring
not work properly anymore. I'm guessing that this may happen since
rows have specific cell references and that when they're deleted, it
throws sequencing off somehow (?).

Is there another way to delete rows without knocking out the
filter-proof alternating row colouring? Seems like a tall order but
hopefully there is something that can be done.

Thanks! D

 

[OssieMac]

WARNING: Ensure that you backup your workbook before installing and running
this code in case it does not do exactly as you want.

I am assuming that it is Conditional Formatting where you have the formula
that you posted. The following code should reset the conditional formatting
for the AutoFilter range after your delete code has run.

Call the sub at the end of your delete code with the following line of code:-

Call ConditFormatAutoFilt

You may want to edit the code to set the fill color. I have set it to
vbYellow.

Feel free to get back to me if you have any problems with it.


Sub ConditFormatAutoFilt()

Dim rngFilter As Range
Dim strAddr1 As String
Dim strAddr2 As String

'Edit "Sheet1" to your worksheet name
With Worksheets("Sheet1")
.Activate
If .AutoFilterMode Then 'Test if filter arrows present.
With .AutoFilter.Range
Set rngFilter = .Offset(1) _
.Resize(.Rows.Count - 1)
End With

With rngFilter
strAddr1 = .Cells(1, 2).Address & ":" & _
.Cells(1, 2).Address(0, 1)
strAddr2 = .Cells(1, 2).Address(0, 1)
.FormatConditions.Delete
.Cells(1, 1).Select
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(MOD(SUBTOTAL(3," & strAddr1 & "),2)=0," _
& strAddr2 & "<>"""")"

'Edit colour to your required color
.FormatConditions(1).Interior.Color = vbYellow

End With
Else
MsgBox "No AutoFilter DropDown arrows on WorkSheet" & _
vbCrLf & "Processing terminated"
Exit Sub
End If

End With
End Sub

 

[OssieMac]

After all my effort to develop code to set the conditional format based on
the AutoFilter range, I find that I can delete rows with no affect on the
alternating row colors. I can delete them individually or in blocks.

I can even insert rows and provided that I populate column B then the
Conditional format works.

Anyway it was a good exercise in applying conditional format to an
AutoFiltered range and the formula provided by yourself is also of benefit.

 

[StargateFan]

WARNING: Ensure that you backup your workbook before installing and running
this code in case it does not do exactly as you want.

I am assuming that it is Conditional Formatting where you have the formula
that you posted. The following code should reset the conditional formatting
for the AutoFilter range after your delete code has run.

Call the sub at the end of your delete code with the following line of code:-

Call ConditFormatAutoFilt

You may want to edit the code to set the fill color. I have set it to
vbYellow.

Feel free to get back to me if you have any problems with it.


Sub ConditFormatAutoFilt()

Dim rngFilter As Range
Dim strAddr1 As String
Dim strAddr2 As String

'Edit "Sheet1" to your worksheet name
With Worksheets("Sheet1")
.Activate
If .AutoFilterMode Then 'Test if filter arrows present.
With .AutoFilter.Range
Set rngFilter = .Offset(1) _
.Resize(.Rows.Count - 1)
End With

With rngFilter
strAddr1 = .Cells(1, 2).Address & ":" & _
.Cells(1, 2).Address(0, 1)
strAddr2 = .Cells(1, 2).Address(0, 1)
.FormatConditions.Delete
.Cells(1, 1).Select
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(MOD(SUBTOTAL(3," & strAddr1 & "),2)=0," _
& strAddr2 & "<>"""")"

'Edit colour to your required color
.FormatConditions(1).Interior.Color = vbYellow

End With
Else
MsgBox "No AutoFilter DropDown arrows on WorkSheet" & _
vbCrLf & "Processing terminated"
Exit Sub
End If

End With
End Sub

Thank you so much for this. I will give it a try.

Apologize for delay in responding; can't find this thread in the
archives via google even when I tried just now with the exact title of
the thread. Happens every so often ... so responding here at home
with my newsreader client.

Thanks! I'll report back. D

 

[StargateFanNotAtHome]

[snip]

Thanks!  I'll report back.  D

[snip]

Hi! Thanks again for the above! I've been working in the workbook
all day and it's working very, very well so far. I had the
opportunity to add new rows and had to delete a few entries so had
quite a few opportunities to see code in action. I added this to the
add and delete rows macro in the workbook and it adjusts the row
colours after each operation. So, so far, so good.

1. I have one challenge that I can't figure out, which was literally
and figuratively highlighted by using vbYellow. I have the
conditional formatting on column A as well, specifically starting at
A3. This code starts at B3 and through the print area. What do I
need to change to get the row colouring to include column beginning
with A3?

2. Just as an aside, I was able to change the colour from vbYellow,
which is a medium yellow, to the light yellow I always use. The
colour choice line now reads like this:

..FormatConditions(1).Interior.Color = RGB(255, 255, 204)

I don't know what the colour choice is in terms of using a similar
format as "vbYellow" as is in the original code, but fortunately, the
archives via google provided the above option of using RGB values. So
I took a screenshot of a cell with my colour choice and found the that
255, 255 and 204 did the job.

Is there perhaps a guide somewhere on what the Excel colours are
called in the VBE, i.e., like "vbYellow" and "vbRed"? I searched via
google for something that might list the colours but nothing came up
even though I made many search attempts.

Thanks! D

 

[OssieMac]

The range for the conditional format is set by the following of code:-
With .AutoFilter.Range
Set rngFilter = .Offset(1) _
.Resize(.Rows.Count - 1)
End With

Column numbers have been left out of the Offset and Resize and hense their
default values are used. (Offset defaults to zero and Resize defaults to
number of columns in AutoFilter.Range.)

The code could be written as this to include the default values for columns:-
With .AutoFilter.Range
Set rngFilter = .Offset(1, 0) _
.Resize(.Rows.Count - 1, .Columns.Count)
End With

The code as is sets conditional format to the actual data below the column
headers of the filtered range. If your conditional formatting is starting on
B3 instead of A3 then do I assume correctly that column A is not included in
the filtered range? If my assumption is correct, give me an example of the
full range address of the filtered range and then what is the range that you
want the conditional format applied to.

You can change the above code to apply conditional format to other ranges by
changing the Offset and Resize. The following example includes one column to
the left of the Filtered Range:-

With .AutoFilter.Range
Set rngFilter = .Offset(1, -1) _
.Resize(.Rows.Count - 1, .Columns.Count + 1)
End With

Note to include one column to left Offset column is -1 and then Resize needs
1 added to columns otherwise the right column of the filtered range is not
included.

You will find the vb color constants under Color Constants in Help. There
are only 8 of them.

RGB function actually returns a number that represents the color. The
following code is an example of finding the actual number that a color
returns and it can be used in lieu of the RGB function. Just use the color
palette to set the interior color of a cell in a worksheet to your preferred
color, ensure that it is the active cell and run the following code to return
the number.

Sub FindcolorCode()
MsgBox ActiveCell.Interior.Color
End Sub

Your RGB color should return 13434879. You can then set the color in VBA as
per the following code:-
.FormatConditions(1).Interior.Color = 13434879

Just for interest, the maths behind the RGB function is as in the following
code:-

Sub RGB_Maths()

Dim lngR as Long 'Red
Dim lngG As Long 'Green
Dim lngB As Long 'Blue
Dim RGBValue As Long

lngR = 255
lngG = 255
lngB = 204

RGBValue = lngR + lngG * 2 ^ 8 + lngB * 2 ^ 16

MsgBox RGBValue

End Sub

 

[StargateFan]

The range for the conditional format is set by the following of code:-
With .AutoFilter.Range
Set rngFilter = .Offset(1) _
.Resize(.Rows.Count - 1)
End With

Column numbers have been left out of the Offset and Resize and hense their
default values are used. (Offset defaults to zero and Resize defaults to
number of columns in AutoFilter.Range.)

Wow, thanks for this neat thread. I'm constantly learning in Excel.

The code could be written as this to include the default values for columns:-
With .AutoFilter.Range
Set rngFilter = .Offset(1, 0) _
.Resize(.Rows.Count - 1, .Columns.Count)
End With

The code as is sets conditional format to the actual data below the column
headers of the filtered range. If your conditional formatting is starting on
B3 instead of A3 then do I assume correctly that column A is not included in
the filtered range? If my assumption is correct, give me an example of the

That's seems to be the case. I didn't notice initially that something
was wrong since my original yellow are the one you used are close
enough that column A still had the colour even if not corect. When I
did notice the difference, I deleted all conditional formatting and
invoked the code column A underneath the header row was left white in
colour.

full range address of the filtered range and then what is the range that you
want the conditional format applied to.

You can change the above code to apply conditional format to other ranges by
changing the Offset and Resize. The following example includes one column to
the left of the Filtered Range:-

With .AutoFilter.Range
Set rngFilter = .Offset(1, -1) _
.Resize(.Rows.Count - 1, .Columns.Count + 1)
End With

Note to include one column to left Offset column is -1 and then Resize needs
1 added to columns otherwise the right column of the filtered range is not
included.

I'll have to play with the above since the written word will not be
clear until I see the results.

Thanks, will give all the above a try. Hopefully I can figure out
what will get A3 to the bottom of column A also included in the
conditional formatting.

You will find the vb color constants under Color Constants in Help. There
are only 8 of them.

Ah, knowing the term will help tremendously!

-----------------------------
Also found a reference online, too:
2009-04-28 19:51:27
(http://groups.google.ca/group/micro...gst&q=values+color+constants#7858b7fdae553401)
Mark,

From: Color Constants in Excel VBA help...

Constant Value

vbBlack 0x0
vbRed 0xFF
vbGreen 0xFF00
vbYellow 0xFFFF
vbBlue 0xFF0000
vbMagenta 0xFF00FF
vbCyan 0xFFFF00
vbWhite 0xFFFFFF

Regards,
Jim Cone
San Francisco, CA
-----------------------------

RGB function actually returns a number that represents the color. The
following code is an example of finding the actual number that a color
returns and it can be used in lieu of the RGB function. Just use the color
palette to set the interior color of a cell in a worksheet to your preferred
color, ensure that it is the active cell and run the following code to return
the number.

Sub FindcolorCode()
MsgBox ActiveCell.Interior.Color
End Sub

Wow, thanks for this! I'm putting this in my PERSONAL.XLS immediately

Do you know what will send the value to the clipboard at the same
time?

Your RGB color should return 13434879. You can then set the color in VBA as
per the following code:-
.FormatConditions(1).Interior.Color = 13434879

Just for interest, the maths behind the RGB function is as in the following
code:-

Sub RGB_Maths()

Dim lngR as Long 'Red
Dim lngG As Long 'Green
Dim lngB As Long 'Blue
Dim RGBValue As Long

lngR = 255
lngG = 255
lngB = 204

RGBValue = lngR + lngG * 2 ^ 8 + lngB * 2 ^ 16

MsgBox RGBValue

End Sub

Thanks once again! Much appreciated. D

 

[OssieMac]

Sub FindcolorCode()
ActiveCell.Interior.Color -- send info to clipboard
MsgBox ActiveCell.Interior.Color
End Sub

I don't know how to do that. Perhaps post a separate question.

 

[StargateFan]

On Tue, 28 Apr 2009 16:09:01 -0700, OssieMac

[snip]

Sub FindcolorCode()
MsgBox ActiveCell.Interior.Color
End Sub

[snip]


Do you know what will send the value to the clipboard at the same
time?

[snip]

I did some more searches but I think I don't know the term so I'm not
finding the solution. Would be good to enlarge the above syntax to
include a line to send the same info to the clipboard, something in
this manner:

Sub FindcolorCode()
ActiveCell.Interior.Color -- send info to clipboard
MsgBox ActiveCell.Interior.Color
End Sub

Thanks! D