Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
When I use conditional formating to get the greenbar effect on my
spreadsheet it wants to print that way and I really only want it to be on my screen for working in the spreadsheet. I want it to print with no colors or patterns. I can't figure out how to do that with the conditional formating so I was thinking using vba would be the best thing to do. I found this bit of code at MrExcel.Com while surfing for the answer to my dilemma. This is run as a macro, needing to push a button (or vba/run) to apply it to your selection. But I don't see anywhere that it is limited to be visible on screen only. Could someone please help me adapt the code to meet my criteria? Sub ApplyGreenBarToSelection() n = 0 For Each VisRow In Selection.Resize(, 1).SpecialCells(xlCellTypeVisible) n = n + 1 If n Mod 2 = 0 Then VisRow.EntireRow.Interior.ColorIndex = 35 End If Next VisRow End Sub Thank You as always Joanne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
How about an alternative that uses the format|conditional formatting plus
another cell. Pick out a cell that you can use as an indicator (I used $A$1). Then the conditional formatting formula could check that cell, too: =AND($A$1<"",MOD(ROW(),2)=1) If you put something (anything!) in A1, then you see the banding. Right before you print, you select A1 and hit the delete key on the keyboard and the banding disappears. Joanne wrote: When I use conditional formating to get the greenbar effect on my spreadsheet it wants to print that way and I really only want it to be on my screen for working in the spreadsheet. I want it to print with no colors or patterns. I can't figure out how to do that with the conditional formating so I was thinking using vba would be the best thing to do. I found this bit of code at MrExcel.Com while surfing for the answer to my dilemma. This is run as a macro, needing to push a button (or vba/run) to apply it to your selection. But I don't see anywhere that it is limited to be visible on screen only. Could someone please help me adapt the code to meet my criteria? Sub ApplyGreenBarToSelection() n = 0 For Each VisRow In Selection.Resize(, 1).SpecialCells(xlCellTypeVisible) n = n + 1 If n Mod 2 = 0 Then VisRow.EntireRow.Interior.ColorIndex = 35 End If Next VisRow End Sub Thank You as always Joanne -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
That's an interesting approach to the problem, and I certainly will
give it a try. My question is, do I need to reset the conditional programming each time I do this as a result of deleting it in A1? Thanks for your interest in my dilemma Joanne Dave Peterson wrote: When I use conditional formating to get the greenbar effect on my spreadsheet it wants to print that way and I really only want it to be on my screen for working in the spreadsheet. I want it to print with no colors or patterns. I can't figure out how to do that with the conditional formating so I was thinking using vba would be the best thing to do. I found this bit of code at MrExcel.Com while surfing for the answer |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
This works really sweet
Thanks Dave Peterson wrote: =AND($A$1<"",MOD(ROW(),2)=1) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
Glad you got it working and you found out that you only had to toggle the value
in that one cell. Joanne wrote: This works really sweet Thanks Dave Peterson wrote: =AND($A$1<"",MOD(ROW(),2)=1) -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
Dave
This is how I have used your cond format code =AND($A$1<"",MOD(ROW(),2)=1) Then, On and Off buttons on toolbar for user with this code behind them: On Button Public Sub FillCell() With Worksheets(1).range("A1") .Value = "On" End With End Sub Off Button Public Sub ClearContents() Dim range Worksheets("tblMain").range("A1").ClearContents End Sub And this all works great, but of course ;-), I want more!! I am wondering if I can do the conditional formatting by code instead so that I can name the range to apply it to - then if my user adds or deletes cols or rows, this little trick will still work. If so, how do I do it. I am also wondering if the value in the indicator cell can be set to visible=false in the code and yet be able to do the job. That way the user never needs to see it toggle on and off, as I will be setting this for several ranges on the same worksheet. Just would look better I think if it can be invisible. Warned you that I want more - I'm a 'would be' coding junkie with woefully little skills! Thanks for your interest in my little project Joanne Dave Peterson wrote: Glad you got it working and you found out that you only had to toggle the value in that one cell. Joanne wrote: This works really sweet Thanks Dave Peterson wrote: =AND($A$1<"",MOD(ROW(),2)=1) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
Duh
I just figured out that the value I put in the indicator cell could be a space and I then have nothing showing. Works really sweet. Joanne wrote: Dave This is how I have used your cond format code =AND($A$1<"",MOD(ROW(),2)=1) Then, On and Off buttons on toolbar for user with this code behind them: On Button Public Sub FillCell() With Worksheets(1).range("A1") .Value = "On" End With End Sub Off Button Public Sub ClearContents() Dim range Worksheets("tblMain").range("A1").ClearContents End Sub And this all works great, but of course ;-), I want more!! I am wondering if I can do the conditional formatting by code instead so that I can name the range to apply it to - then if my user adds or deletes cols or rows, this little trick will still work. If so, how do I do it. I am also wondering if the value in the indicator cell can be set to visible=false in the code and yet be able to do the job. That way the user never needs to see it toggle on and off, as I will be setting this for several ranges on the same worksheet. Just would look better I think if it can be invisible. Warned you that I want more - I'm a 'would be' coding junkie with woefully little skills! Thanks for your interest in my little project Joanne Dave Peterson wrote: Glad you got it working and you found out that you only had to toggle the value in that one cell. Joanne wrote: This works really sweet Thanks Dave Peterson wrote: =AND($A$1<"",MOD(ROW(),2)=1) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
You could also use a custom format of ;;; (3 semicolons).
And if you could pick out a column that always has data when that row is used, you could use something like: Option Explicit Sub testme() Dim myRng As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("a1:x" & LastRow) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND($A$1<"""",MOD(ROW(),2)=1)" .FormatConditions(1).Interior.ColorIndex = 35 End With End With End Sub I used column A as my indicator column and formatted A:X (and 35 is light green in my workbook). Joanne wrote: Duh I just figured out that the value I put in the indicator cell could be a space and I then have nothing showing. Works really sweet. Joanne wrote: Dave This is how I have used your cond format code =AND($A$1<"",MOD(ROW(),2)=1) Then, On and Off buttons on toolbar for user with this code behind them: On Button Public Sub FillCell() With Worksheets(1).range("A1") .Value = "On" End With End Sub Off Button Public Sub ClearContents() Dim range Worksheets("tblMain").range("A1").ClearContents End Sub And this all works great, but of course ;-), I want more!! I am wondering if I can do the conditional formatting by code instead so that I can name the range to apply it to - then if my user adds or deletes cols or rows, this little trick will still work. If so, how do I do it. I am also wondering if the value in the indicator cell can be set to visible=false in the code and yet be able to do the job. That way the user never needs to see it toggle on and off, as I will be setting this for several ranges on the same worksheet. Just would look better I think if it can be invisible. Warned you that I want more - I'm a 'would be' coding junkie with woefully little skills! Thanks for your interest in my little project Joanne Dave Peterson wrote: Glad you got it working and you found out that you only had to toggle the value in that one cell. Joanne wrote: This works really sweet Thanks Dave Peterson wrote: =AND($A$1<"",MOD(ROW(),2)=1) -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
Thanks for the code Dave.
Couple questions please what does A:X do for the routine? I understand .range("A1" & LastRow), but again, what is the :x added in there for? Also, as I understand the code when reading it, it will apply to the entire worksheet. My users need to apply it to 3 or 4 different ranges of the same worksheet, so I need to know how to code it using a range 'name' that will allow expansion and deletion of rows or columns (at least this is how I understand what I read in the help files regarding the naming of ranges and it's usefullness) Dave Peterson wrote: You could also use a custom format of ;;; (3 semicolons). And if you could pick out a column that always has data when that row is used, you could use something like: Option Explicit Sub testme() Dim myRng As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("a1:x" & LastRow) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND($A$1<"""",MOD(ROW(),2)=1)" .FormatConditions(1).Interior.ColorIndex = 35 End With End With End Sub I used column A as my indicator column and formatted A:X (and 35 is light green in my workbook). Joanne wrote: Duh I just figured out that the value I put in the indicator cell could be a space and I then have nothing showing. Works really sweet. Joanne wrote: Dave This is how I have used your cond format code =AND($A$1<"",MOD(ROW(),2)=1) Then, On and Off buttons on toolbar for user with this code behind them: On Button Public Sub FillCell() With Worksheets(1).range("A1") .Value = "On" End With End Sub Off Button Public Sub ClearContents() Dim range Worksheets("tblMain").range("A1").ClearContents End Sub And this all works great, but of course ;-), I want more!! I am wondering if I can do the conditional formatting by code instead so that I can name the range to apply it to - then if my user adds or deletes cols or rows, this little trick will still work. If so, how do I do it. I am also wondering if the value in the indicator cell can be set to visible=false in the code and yet be able to do the job. That way the user never needs to see it toggle on and off, as I will be setting this for several ranges on the same worksheet. Just would look better I think if it can be invisible. Warned you that I want more - I'm a 'would be' coding junkie with woefully little skills! Thanks for your interest in my little project Joanne Dave Peterson wrote: Glad you got it working and you found out that you only had to toggle the value in that one cell. Joanne wrote: This works really sweet Thanks Dave Peterson wrote: =AND($A$1<"",MOD(ROW(),2)=1) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
I thought that along with your requirement to only conditionally format certain
rows, you'd want to only conditionally format certain columns. And in my code, I was only working on columns A:X. But be careful. C may not be what you mean. If lastrow is 234 (say), then this ..range("A1" & LastRow) would be equivalent to: ..range("A1" & 234) or ..range("A1234") (Just that one cell) Debra Dalgleish shows how to use a dynamic range he http://www.contextures.com/xlNames01.html#Dynamic You'll have to adjust the formula to only look at the cells that are included in each of the 4 ranges. Debra's sample name looks at all of column A. Joanne wrote: Thanks for the code Dave. Couple questions please what does A:X do for the routine? I understand .range("A1" & LastRow), but again, what is the :x added in there for? Also, as I understand the code when reading it, it will apply to the entire worksheet. My users need to apply it to 3 or 4 different ranges of the same worksheet, so I need to know how to code it using a range 'name' that will allow expansion and deletion of rows or columns (at least this is how I understand what I read in the help files regarding the naming of ranges and it's usefullness) Dave Peterson wrote: You could also use a custom format of ;;; (3 semicolons). And if you could pick out a column that always has data when that row is used, you could use something like: Option Explicit Sub testme() Dim myRng As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("a1:x" & LastRow) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND($A$1<"""",MOD(ROW(),2)=1)" .FormatConditions(1).Interior.ColorIndex = 35 End With End With End Sub I used column A as my indicator column and formatted A:X (and 35 is light green in my workbook). Joanne wrote: Duh I just figured out that the value I put in the indicator cell could be a space and I then have nothing showing. Works really sweet. Joanne wrote: Dave This is how I have used your cond format code =AND($A$1<"",MOD(ROW(),2)=1) Then, On and Off buttons on toolbar for user with this code behind them: On Button Public Sub FillCell() With Worksheets(1).range("A1") .Value = "On" End With End Sub Off Button Public Sub ClearContents() Dim range Worksheets("tblMain").range("A1").ClearContents End Sub And this all works great, but of course ;-), I want more!! I am wondering if I can do the conditional formatting by code instead so that I can name the range to apply it to - then if my user adds or deletes cols or rows, this little trick will still work. If so, how do I do it. I am also wondering if the value in the indicator cell can be set to visible=false in the code and yet be able to do the job. That way the user never needs to see it toggle on and off, as I will be setting this for several ranges on the same worksheet. Just would look better I think if it can be invisible. Warned you that I want more - I'm a 'would be' coding junkie with woefully little skills! Thanks for your interest in my little project Joanne Dave Peterson wrote: Glad you got it working and you found out that you only had to toggle the value in that one cell. Joanne wrote: This works really sweet Thanks Dave Peterson wrote: =AND($A$1<"",MOD(ROW(),2)=1) -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
coding greenbar
Stupid fingers:
But be careful. ..range("A1" & LastRow) may not be what you mean. (C was supposed to be ctrl-c. I must have hit Shift-C. Doh!) Dave Peterson wrote: I thought that along with your requirement to only conditionally format certain rows, you'd want to only conditionally format certain columns. And in my code, I was only working on columns A:X. But be careful. C may not be what you mean. If lastrow is 234 (say), then this .range("A1" & LastRow) would be equivalent to: .range("A1" & 234) or .range("A1234") (Just that one cell) Debra Dalgleish shows how to use a dynamic range he http://www.contextures.com/xlNames01.html#Dynamic You'll have to adjust the formula to only look at the cells that are included in each of the 4 ranges. Debra's sample name looks at all of column A. Joanne wrote: Thanks for the code Dave. Couple questions please what does A:X do for the routine? I understand .range("A1" & LastRow), but again, what is the :x added in there for? Also, as I understand the code when reading it, it will apply to the entire worksheet. My users need to apply it to 3 or 4 different ranges of the same worksheet, so I need to know how to code it using a range 'name' that will allow expansion and deletion of rows or columns (at least this is how I understand what I read in the help files regarding the naming of ranges and it's usefullness) Dave Peterson wrote: You could also use a custom format of ;;; (3 semicolons). And if you could pick out a column that always has data when that row is used, you could use something like: Option Explicit Sub testme() Dim myRng As Range Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("a1:x" & LastRow) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND($A$1<"""",MOD(ROW(),2)=1)" .FormatConditions(1).Interior.ColorIndex = 35 End With End With End Sub I used column A as my indicator column and formatted A:X (and 35 is light green in my workbook). Joanne wrote: Duh I just figured out that the value I put in the indicator cell could be a space and I then have nothing showing. Works really sweet. Joanne wrote: Dave This is how I have used your cond format code =AND($A$1<"",MOD(ROW(),2)=1) Then, On and Off buttons on toolbar for user with this code behind them: On Button Public Sub FillCell() With Worksheets(1).range("A1") .Value = "On" End With End Sub Off Button Public Sub ClearContents() Dim range Worksheets("tblMain").range("A1").ClearContents End Sub And this all works great, but of course ;-), I want more!! I am wondering if I can do the conditional formatting by code instead so that I can name the range to apply it to - then if my user adds or deletes cols or rows, this little trick will still work. If so, how do I do it. I am also wondering if the value in the indicator cell can be set to visible=false in the code and yet be able to do the job. That way the user never needs to see it toggle on and off, as I will be setting this for several ranges on the same worksheet. Just would look better I think if it can be invisible. Warned you that I want more - I'm a 'would be' coding junkie with woefully little skills! Thanks for your interest in my little project Joanne Dave Peterson wrote: Glad you got it working and you found out that you only had to toggle the value in that one cell. Joanne wrote: This works really sweet Thanks Dave Peterson wrote: =AND($A$1<"",MOD(ROW(),2)=1) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic greenbar effect, varied color, corrects with re-format | Excel Worksheet Functions | |||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming | |||
Implant macro coding into ASP coding | Excel Programming | |||
how do I create greenbar paper simulation in Excel? | Excel Programming | |||
how do I create greenbar paper simulation in Excel? | Excel Programming |