Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filterable alternating row colour formula skewed when rows deleted.
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)?? :oD", 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! :oD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filterable alternating row colour formula skewed when rows deleted
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 -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filterable alternating row colour formula skewed when rows deleted
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. -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filterable alternating row colour formula skewed when rows deleted
On Fri, 24 Apr 2009 00:18:05 -0700, OssieMac
wrote: 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. :oD |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filterable alternating row colour formula skewed when rowsdeleted
[snip]
Thanks! *I'll report back. *:oD [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! :oD |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filterable alternating row colour formula skewed when rows del
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 -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filterable alternating row colour formula skewed when rows del
On Tue, 28 Apr 2009 16:09:01 -0700, OssieMac
wrote: 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. I don't think it'll ever stop <g. 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/micros...58b7fdae553401) 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. :oD |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send colour constant info also to the clipboard, adjusting code?
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. -- Regards, OssieMac |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send colour constant info also to the clipboard, adjusting code?
On Tue, 28 Apr 2009 20:08:18 -0500, StargateFan
wrote: On Tue, 28 Apr 2009 16:09:01 -0700, OssieMac wrote: [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! :oD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change colour of cells when content is altered/changed BUT NOT TO INSERTED OR DELETED ROWS | Excel Discussion (Misc queries) | |||
colour rows alternating colour | Excel Discussion (Misc queries) | |||
Alternating row colour according to month | Excel Discussion (Misc queries) | |||
looking for shape of right-skewed, left-skewed symmetric how do | Setting up and Configuration of Excel | |||
alternating cell colour | New Users to Excel |