Hi there I need a macro that will select the value in lets say A1 and then perform a cell shade on the value of A1 to that many rows. Now I found this,
but that only works on what the user selects with the mouse.Sub ShadeEveryOtherRow() Dim Counter As Integer 'For every row in the current selection... For Counter = 1 To Selection.Rows.Count 'If the row is an odd number (within the selection)... If Counter Mod 2 = 1 Then 'Set the pattern to xlGray16. Selection.Rows(Counter).Interior.Pattern = xlGray16 End If Next End Sub
I want it to work based on what the cell value says, so if a1 = 3 then I want 3 cells to be shaded.
it has to work from a number of cells ranging from A1 through to AA2.
IE,
Book1.xls
Last edited by royUK; 11-23-2008 at 03:00 AM. Reason: add code tags
I'm not sure of the row selection code, but the code you need to make the formatting gray for every OTHER row in an already selected range is:
Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 15
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
merilvingian
Please take the time to read he Forum Rules & use Code Tags in future.
If A1 contains 5 do you want five rows shaded and where do you want he shading to start?
Last edited by royUK; 11-23-2008 at 03:15 AM.
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Sorry I did not read your complete post. Sorry.Originally Posted by royUK
um if A1 contains 5 I want the shading to start directly below A1, so on A2 5 rows down but every second row. If you have a look at the attachment you will see what I am aiming for. I just used the macro I have for now and manually selected the rows.
Thanks in advance.
Any Ideas people? This is still unresolved.
How many different colors do you need?
Play with this and see if it will work.
Sub in the values you need Here
If Cell.Value = "3" and then
here:
Cell.Value <> "3"
It is setup to work on column A , Rows 1-800
Private Sub Worksheet_Change(ByVal Target As Range) Set MyPlageSheet2 = Range("A1:A800") For Each Cell In MyPlageSheet2 If Cell.Value = "3" Then Cell.Resize(, 4).Interior.ColorIndex = 3 End If If Cell.Value = "4" Then Cell.Resize(, 4).Interior.ColorIndex = 4 End If If Cell.Value = "43" Then Cell.Resize(, 4).Interior.ColorIndex = 43 End If If Cell.Value = "36" Then Cell.Resize(, 4).Interior.ColorIndex = 36 End If If Cell.Value = "40" Then Cell.Resize(, 4).Interior.ColorIndex = 40 End If If Cell.Value = "45" Then Cell.Resize(, 4).Interior.ColorIndex = 45 End If If Cell.Value = "42" Then Cell.Resize(, 4).Interior.ColorIndex = 42 End If If Cell.Value = "2" Then Cell.Resize(, 4).Interior.ColorIndex = 2 End If If Cell.Value <> "3" And Cell.Value <> "4" And Cell.Value <> "43" And Cell.Value <> "36" And Cell.Value <> "40" And Cell.Value <> "45" And Cell.Value <> "42" And Cell.Value <> "2" Then Cell.Interior.ColorIndex = xlNone End If Next End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks