Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
To anyone,
This is the current formula that I am using to highlight rows with: -- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow ..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) ..LineStyle = xlContinuous ..Weight = xlThin ..ColorIndex = 5 End With With .Borders(xlBottom) ..LineStyle = xlContinuous ..Weight = xlThin ..ColorIndex = 5 End With End With ..FormatConditions(1).Interior.ColorIndex = 8 End With End Sub Works well...problem, is it removes any coditions that exsist in oter areas of the SS. And when I protect the page, the macro no longer works. Is there any way to modify this command so that a column is skiped? For instance; I want the entire row with the exception of column "x". Can this be done? Any help would be awesome!!!! Thanks, JARoman |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
i would leave your macro alone, with the exception of adding this at
the bottom (after "End With"): worksheets("Sheet1").range("x:x").ClearFormats you may need to ammend the sheet name or whatever to make it work in your macro. but that would be the simplest idea, i believe. make sure you save a copy of your worksheet before trying it, in case it doesn't do what you want it to. of course this will also remove formats that had been there previously, so it may not be what you want. hope that helps! :) susan On Jan 21, 10:14*am, Xman wrote: To anyone, This is the current formula that I am using to highlight rows with: -- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With End With .FormatConditions(1).Interior.ColorIndex = 8 End With End Sub Works well...problem, is it removes any coditions that exsist in oter areas of the SS. And when I protect the page, the macro no longer works. Is there any way to modify this command so that a column is skiped? *For instance; I want the entire row with the exception of column "x". *Can this be done? Any help would be awesome!!!! *Thanks, JARoman |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
Hi Susan,
Thank you for your suggestion.....It worked great on a blank spread sheet. But when I applied it to the one I'm working on, it just didn't work. I'm very new to this whole VB stuff, but hopefully with folks like you willing to assist I'll find the correct answer. Thanks again. -- JARoman "Susan" wrote: i would leave your macro alone, with the exception of adding this at the bottom (after "End With"): worksheets("Sheet1").range("x:x").ClearFormats you may need to ammend the sheet name or whatever to make it work in your macro. but that would be the simplest idea, i believe. make sure you save a copy of your worksheet before trying it, in case it doesn't do what you want it to. of course this will also remove formats that had been there previously, so it may not be what you want. hope that helps! :) susan On Jan 21, 10:14 am, Xman wrote: To anyone, This is the current formula that I am using to highlight rows with: -- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With End With .FormatConditions(1).Interior.ColorIndex = 8 End With End Sub Works well...problem, is it removes any coditions that exsist in oter areas of the SS. And when I protect the page, the macro no longer works. Is there any way to modify this command so that a column is skiped? For instance; I want the entire row with the exception of column "x". Can this be done? Any help would be awesome!!!! Thanks, JARoman |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
it did absolutely nothing at all??? but it worked in a new
worksheet? that would imply that your sheet name is not "Sheet1", which it would be in a new workbook. if it's named something different, you'll have to enter the correct name in the macro line. susan On Jan 21, 12:10*pm, Xman wrote: Hi Susan, Thank you for your suggestion.....It worked great on a blank spread sheet.. * But when I applied it to the one I'm working on, it just didn't work. *I'm very new to this whole VB stuff, but hopefully with folks like you willing to assist I'll find the correct answer. *Thanks again. -- JARoman "Susan" wrote: i would leave your macro alone, with the exception of adding this at the bottom (after "End With"): worksheets("Sheet1").range("x:x").ClearFormats you may need to ammend the sheet name or whatever to make it work in your macro. *but that would be the simplest idea, i believe. make sure you save a copy of your worksheet before trying it, in case it doesn't do what you want it to. *of course this will also remove formats that had been there previously, so it may not be what you want. hope that helps! :) susan On Jan 21, 10:14 am, Xman wrote: To anyone, This is the current formula that I am using to highlight rows with: -- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With End With .FormatConditions(1).Interior.ColorIndex = 8 End With End Sub Works well...problem, is it removes any coditions that exsist in oter areas of the SS. And when I protect the page, the macro no longer works. Is there any way to modify this command so that a column is skiped? *For instance; I want the entire row with the exception of column "x". *Can this be done? Any help would be awesome!!!! *Thanks, JARoman- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
Hi Susan.....you are right! I had named the sheet something other than
"sheet...." and therfore did not work. You are also right in saying that any conditions or formats will be eliminated from that column, thus the reason for the original question, but it's getting closer. Now....if I can just get those conditions and formats back in there. Thank you..... -- JARoman "Susan" wrote: it did absolutely nothing at all??? but it worked in a new worksheet? that would imply that your sheet name is not "Sheet1", which it would be in a new workbook. if it's named something different, you'll have to enter the correct name in the macro line. susan On Jan 21, 12:10 pm, Xman wrote: Hi Susan, Thank you for your suggestion.....It worked great on a blank spread sheet.. But when I applied it to the one I'm working on, it just didn't work. I'm very new to this whole VB stuff, but hopefully with folks like you willing to assist I'll find the correct answer. Thanks again. -- JARoman "Susan" wrote: i would leave your macro alone, with the exception of adding this at the bottom (after "End With"): worksheets("Sheet1").range("x:x").ClearFormats you may need to ammend the sheet name or whatever to make it work in your macro. but that would be the simplest idea, i believe. make sure you save a copy of your worksheet before trying it, in case it doesn't do what you want it to. of course this will also remove formats that had been there previously, so it may not be what you want. hope that helps! :) susan On Jan 21, 10:14 am, Xman wrote: To anyone, This is the current formula that I am using to highlight rows with: -- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With End With .FormatConditions(1).Interior.ColorIndex = 8 End With End Sub Works well...problem, is it removes any coditions that exsist in oter areas of the SS. And when I protect the page, the macro no longer works. Is there any way to modify this command so that a column is skiped? For instance; I want the entire row with the exception of column "x". Can this be done? Any help would be awesome!!!! Thanks, JARoman- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
Susan
If you use this Me.range("x:x").ClearFormats Me refers to the activesheet so sheetname not required. Gord Dibben MS Excel MVP On Wed, 21 Jan 2009 09:44:51 -0800 (PST), Susan wrote: it did absolutely nothing at all??? but it worked in a new worksheet? that would imply that your sheet name is not "Sheet1", which it would be in a new workbook. if it's named something different, you'll have to enter the correct name in the macro line. susan On Jan 21, 12:10*pm, Xman wrote: Hi Susan, Thank you for your suggestion.....It worked great on a blank spread sheet. * But when I applied it to the one I'm working on, it just didn't work. *I'm very new to this whole VB stuff, but hopefully with folks like you willing to assist I'll find the correct answer. *Thanks again. -- JARoman "Susan" wrote: i would leave your macro alone, with the exception of adding this at the bottom (after "End With"): worksheets("Sheet1").range("x:x").ClearFormats you may need to ammend the sheet name or whatever to make it work in your macro. *but that would be the simplest idea, i believe. make sure you save a copy of your worksheet before trying it, in case it doesn't do what you want it to. *of course this will also remove formats that had been there previously, so it may not be what you want. hope that helps! :) susan On Jan 21, 10:14 am, Xman wrote: To anyone, This is the current formula that I am using to highlight rows with: -- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With End With .FormatConditions(1).Interior.ColorIndex = 8 End With End Sub Works well...problem, is it removes any coditions that exsist in oter areas of the SS. And when I protect the page, the macro no longer works. Is there any way to modify this command so that a column is skiped? *For instance; I want the entire row with the exception of column "x". *Can this be done? Any help would be awesome!!!! *Thanks, JARoman- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
This may get you a little closer but does clear some formatting on just the
selected row. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="justme" If Not OldCell Is Nothing Then With OldCell.EntireRow .Interior.ColorIndex = xlColorIndexNone .Borders.LineStyle = xlLineStyleNone End With End If Set OldCell = Target With OldCell.EntireRow .Interior.ColorIndex = 6 .Borders.LineStyle = xlContinuous End With Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If Me.Range("X:X").ClearFormats ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP On Wed, 21 Jan 2009 09:59:01 -0800, Xman wrote: Hi Susan.....you are right! I had named the sheet something other than "sheet...." and therfore did not work. You are also right in saying that any conditions or formats will be eliminated from that column, thus the reason for the original question, but it's getting closer. Now....if I can just get those conditions and formats back in there. Thank you..... |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
Gord -
rats, didn't think of that. :) susan On Jan 21, 3:50*pm, Gord Dibben <gorddibbATshawDOTca wrote: Susan If you use this * * * Me.range("x:x").ClearFormats Me * *refers to the activesheet so sheetname not required. Gord Dibben *MS Excel MVP |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
Thanks guys for all your help. Gord, I'm getting ready to try out your
formula with Susans' suggestion. I'll keep you all posted. Thank you, -- JARoman "Gord Dibben" wrote: This may get you a little closer but does clear some formatting on just the selected row. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="justme" If Not OldCell Is Nothing Then With OldCell.EntireRow .Interior.ColorIndex = xlColorIndexNone .Borders.LineStyle = xlLineStyleNone End With End If Set OldCell = Target With OldCell.EntireRow .Interior.ColorIndex = 6 .Borders.LineStyle = xlContinuous End With Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If Me.Range("X:X").ClearFormats ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP On Wed, 21 Jan 2009 09:59:01 -0800, Xman wrote: Hi Susan.....you are right! I had named the sheet something other than "sheet...." and therfore did not work. You are also right in saying that any conditions or formats will be eliminated from that column, thus the reason for the original question, but it's getting closer. Now....if I can just get those conditions and formats back in there. Thank you..... |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
Gord, your formula did allow me to lock the worksheet, but as you stated..it
erased everything as I moved the highlight.The original formula with Susans helpful addition would be ideal if I could get it to leave the conditional formats I have in column "x" alone and if I could get it to work when it's protected it would be perfect. This has become a real obssesion for me because I know there has to be a way to do it. I'm gonna keep trying based on the original formula. I really appreciate all of your imput, Gord/Susan. Thanks -- JARoman "Gord Dibben" wrote: This may get you a little closer but does clear some formatting on just the selected row. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="justme" If Not OldCell Is Nothing Then With OldCell.EntireRow .Interior.ColorIndex = xlColorIndexNone .Borders.LineStyle = xlLineStyleNone End With End If Set OldCell = Target With OldCell.EntireRow .Interior.ColorIndex = 6 .Borders.LineStyle = xlContinuous End With Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If Me.Range("X:X").ClearFormats ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP On Wed, 21 Jan 2009 09:59:01 -0800, Xman wrote: Hi Susan.....you are right! I had named the sheet something other than "sheet...." and therfore did not work. You are also right in saying that any conditions or formats will be eliminated from that column, thus the reason for the original question, but it's getting closer. Now....if I can just get those conditions and formats back in there. Thank you..... |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
the only thing i can think to do is to record a macro re-installing
the conditional formats you want in column X. when you record the macro it will put it in a separate module. then, below the clearing of all formats in column X, put Call Macro2 'or change it to whatever the name of 'the macro you recorded is then it will re-install all the conditional formats. a pain to do, but at least you'd only have to do it once & then the macro would automatically do it for you each time. :) susan On Jan 22, 12:07*pm, Xman wrote: Gord, your formula did allow me to lock the worksheet, but as you stated...it erased everything as I moved the highlight.The original formula with Susans helpful addition would be ideal if I could get it to leave the conditional formats I have in column "x" alone and if I could get it to work when it's protected it would be perfect. This has become a real obssesion for me because I know there has to be a way to do it. *I'm gonna keep trying based on the original formula. *I really appreciate all of your imput, Gord/Susan. Thanks -- JARoman "Gord Dibben" wrote: This may get you a little closer but does clear some formatting on just the selected row. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="justme" * * If Not OldCell Is Nothing Then * * With OldCell.EntireRow * * * *.Interior.ColorIndex = xlColorIndexNone * * * *.Borders.LineStyle = xlLineStyleNone * * End With * * End If * * Set OldCell = Target * * With OldCell.EntireRow * * * *.Interior.ColorIndex = 6 * * * *.Borders.LineStyle = xlContinuous * * End With Else * * If OldCell Is Nothing Then * * * * Set OldCell = Target * * Else * * Set OldCell = Union(OldCell, Target) * * End If End If Me.Range("X:X").ClearFormats ActiveSheet.Protect Password:="justme" End Sub Gord Dibben *MS Excel MVP On Wed, 21 Jan 2009 09:59:01 -0800, Xman wrote: Hi Susan.....you are right! *I had named the sheet something other than "sheet...." and therfore did not work. *You are also right in saying that any conditions or formats will be eliminated from that column, thus the reason for the original question, but it's getting closer. *Now....if I can just get those conditions and formats back in there. Thank you.....- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
I misunderstood. I thought you wanted the formats cleared from column X and
used Susan's Range("X:X").ClearFormats The code I posted will not clear the CF formatting in any column or cell if you remove the line Me.Range("X:X").ClearFormats which I have done in this revision. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="justme" If Not OldCell Is Nothing Then With OldCell.EntireRow .Interior.ColorIndex = xlColorIndexNone .Borders.LineStyle = xlLineStyleNone End With End If Set OldCell = Target With OldCell.EntireRow .Interior.ColorIndex = 6 .Borders.LineStyle = xlContinuous End With Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If ActiveSheet.Protect Password:="justme" End Sub Gord On Thu, 22 Jan 2009 09:07:02 -0800, Xman wrote: Gord, your formula did allow me to lock the worksheet, but as you stated..it erased everything as I moved the highlight.The original formula with Susans helpful addition would be ideal if I could get it to leave the conditional formats I have in column "x" alone and if I could get it to work when it's protected it would be perfect. This has become a real obssesion for me because I know there has to be a way to do it. I'm gonna keep trying based on the original formula. I really appreciate all of your imput, Gord/Susan. Thanks |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Rows with VB.
You're brilliant!!!!!! It's working the way I want it too.....with the shhet
protected it works, the conditional formats remain in the x column. But why does it wipe out my sheet of all gridlines and color schemes, kind of like an eraser. My sheet turns entirely white with the exception of any fonts and the CF colors? -- JARoman "Gord Dibben" wrote: I misunderstood. I thought you wanted the formats cleared from column X and used Susan's Range("X:X").ClearFormats The code I posted will not clear the CF formatting in any column or cell if you remove the line Me.Range("X:X").ClearFormats which I have done in this revision. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="justme" If Not OldCell Is Nothing Then With OldCell.EntireRow .Interior.ColorIndex = xlColorIndexNone .Borders.LineStyle = xlLineStyleNone End With End If Set OldCell = Target With OldCell.EntireRow .Interior.ColorIndex = 6 .Borders.LineStyle = xlContinuous End With Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If ActiveSheet.Protect Password:="justme" End Sub Gord On Thu, 22 Jan 2009 09:07:02 -0800, Xman wrote: Gord, your formula did allow me to lock the worksheet, but as you stated..it erased everything as I moved the highlight.The original formula with Susans helpful addition would be ideal if I could get it to leave the conditional formats I have in column "x" alone and if I could get it to work when it's protected it would be perfect. This has become a real obssesion for me because I know there has to be a way to do it. I'm gonna keep trying based on the original formula. I really appreciate all of your imput, Gord/Susan. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlighting Rows | Excel Discussion (Misc queries) | |||
Highlighting Rows | Excel Discussion (Misc queries) | |||
Highlighting Has Shifted on Rows | Excel Discussion (Misc queries) | |||
highlighting rows according to a input value? | Excel Discussion (Misc queries) | |||
Highlighting rows? | Setting up and Configuration of Excel |