Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > What's wrong with this macro?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default What's wrong with this macro?

I created this beautiful spreadsheet to manage time for my office, for
which someone on here helped me make a macro. It's in the code for the
sheet entitled "Summary". The user is supposed to make changes in the
name or department on the Summary sheet and the row is supposed to
change color according to the department. Also, the sheet is supposed
to re-sort by department and then by name. Also, the sheet for every
month is supposed to sort accordingly.

I thought the sheet was sorting okay, but now that I have data entered
(time) in the individual month sheets, when I make a change on Summary
and the name moves on the list when it is automatically sorted, the
time that goes with that name remains in the same place and is not
sorted. I have uploaded a copy of the spreadsheet 'here'
(http://s64.yousendit.com/d.aspx?id=1...12MGFZCZ8MFSL)... if
anyone could just take a look at it and let me know what's wrong with
it, that would be great. Thank you.


If link above does not work try 'this one'
(http://s61.yousendit.com/d.aspx?id=1...V0WLE9K939L05X)


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile: http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=504175

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,316
Default What's wrong with this macro?
You would be better off pasting the code into the body of the post, I don't
think you'll get many takers on opening a spreadsheet with VBA code from an
unknown source on this website.
--
Kevin Backmann


"belly0fdesire" wrote:


I created this beautiful spreadsheet to manage time for my office, for
which someone on here helped me make a macro. It's in the code for the
sheet entitled "Summary". The user is supposed to make changes in the
name or department on the Summary sheet and the row is supposed to
change color according to the department. Also, the sheet is supposed
to re-sort by department and then by name. Also, the sheet for every
month is supposed to sort accordingly.

I thought the sheet was sorting okay, but now that I have data entered
(time) in the individual month sheets, when I make a change on Summary
and the name moves on the list when it is automatically sorted, the
time that goes with that name remains in the same place and is not
sorted. I have uploaded a copy of the spreadsheet 'here'
(http://s64.yousendit.com/d.aspx?id=1...12MGFZCZ8MFSL)... if
anyone could just take a look at it and let me know what's wrong with
it, that would be great. Thank you.


If link above does not work try 'this one'
(http://s61.yousendit.com/d.aspx?id=1...V0WLE9K939L05X)


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile: http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=504175


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default What's wrong with this macro?

Okay. Thank you for the advice. I have included the code below... I
don't know if anyone will be able to help without just downloading the
file. There's nothing to be afraid or worried about. It's a very
impressive spreadsheet that I've designed and I'm pretty proud of it,
but I'm just not very good with VBA code... if anyone could help, it
would be very very appreciated...



Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iClr As Integer

On Error Resume Next

If Target.Count = 1 And (Target.Column = 2 Or Target.Column = 6) Then
Range("A6:O120").Sort Key1:=Range("F6"), Order1:=xlAscending, Key2:=Range("B6") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

'All months
Sheets("Jan").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Jan!B7,Summary!B:B,0 ))"
Sheets("Jan").Range("a7").AutoFill Destination:=Sheets("Jan").Range("a7:a121")
Sheets("Jan").Range("a7:a121").Value = Sheets("Jan").Range("a7:a121").Value
Sheets("Jan").Rows("7:121").Sort Key1:=Sheets("Jan").Range("a7")
Sheets("Feb").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Feb!B7,Summary!B:B,0 ))"
Sheets("Feb").Range("a7").AutoFill Destination:=Sheets("Feb").Range("a7:a121")
Sheets("Feb").Range("a7:a121").Value = Sheets("Feb").Range("a7:a121").Value
Sheets("Feb").Rows("7:121").Sort Key1:=Sheets("Feb").Range("a7")
Sheets("Mar").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Mar!B7,Summary!B:B,0 ))"
Sheets("Mar").Range("a7").AutoFill Destination:=Sheets("Mar").Range("a7:a121")
Sheets("Mar").Range("a7:a121").Value = Sheets("Mar").Range("a7:a121").Value
Sheets("Mar").Rows("7:121").Sort Key1:=Sheets("Mar").Range("a7")
Sheets("Apr").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Apr!B7,Summary!B:B,0 ))"
Sheets("Apr").Range("a7").AutoFill Destination:=Sheets("Apr").Range("a7:a121")
Sheets("Apr").Range("a7:a121").Value = Sheets("Apr").Range("a7:a121").Value
Sheets("Apr").Rows("7:121").Sort Key1:=Sheets("Apr").Range("a7")
Sheets("May").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(May!B7,Summary!B:B,0 ))"
Sheets("May").Range("a7").AutoFill Destination:=Sheets("May").Range("a7:a121")
Sheets("May").Range("a7:a121").Value = Sheets("May").Range("a7:a121").Value
Sheets("May").Rows("7:121").Sort Key1:=Sheets("May").Range("a7")
Sheets("Jun").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Jun!B7,Summary!B:B,0 ))"
Sheets("Jun").Range("a7").AutoFill Destination:=Sheets("Jun").Range("a7:a121")
Sheets("Jun").Range("a7:a121").Value = Sheets("Jun").Range("a7:a121").Value
Sheets("Jun").Rows("7:121").Sort Key1:=Sheets("Jun").Range("a7")
Sheets("Jul").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Jul!B7,Summary!B:B,0 ))"
Sheets("Jul").Range("a7").AutoFill Destination:=Sheets("Jul").Range("a7:a121")
Sheets("Jul").Range("a7:a121").Value = Sheets("Jul").Range("a7:a121").Value
Sheets("Jul").Rows("7:121").Sort Key1:=Sheets("Jul").Range("a7")
Sheets("Aug").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Aug!B7,Summary!B:B,0 ))"
Sheets("Aug").Range("a7").AutoFill Destination:=Sheets("Aug").Range("a7:a121")
Sheets("Aug").Range("a7:a121").Value = Sheets("Aug").Range("a7:a121").Value
Sheets("Aug").Rows("7:121").Sort Key1:=Sheets("Aug").Range("a7")
Sheets("Sep").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Sep!B7,Summary!B:B,0 ))"
Sheets("Sep").Range("a7").AutoFill Destination:=Sheets("Sep").Range("a7:a121")
Sheets("Sep").Range("a7:a121").Value = Sheets("Sep").Range("a7:a121").Value
Sheets("Sep").Rows("7:121").Sort Key1:=Sheets("Sep").Range("a7")
Sheets("Oct").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Oct!B7,Summary!B:B,0 ))"
Sheets("Oct").Range("a7").AutoFill Destination:=Sheets("Oct").Range("a7:a121")
Sheets("Oct").Range("a7:a121").Value = Sheets("Oct").Range("a7:a121").Value
Sheets("Oct").Rows("7:121").Sort Key1:=Sheets("Oct").Range("a7")
Sheets("Nov").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Nov!B7,Summary!B:B,0 ))"
Sheets("Nov").Range("a7").AutoFill Destination:=Sheets("Nov").Range("a7:a121")
Sheets("Nov").Range("a7:a121").Value = Sheets("Nov").Range("a7:a121").Value
Sheets("Nov").Rows("7:121").Sort Key1:=Sheets("Nov").Range("a7")
Sheets("Dec").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Dec!B7,Summary!B:B,0 ))"
Sheets("Dec").Range("a7").AutoFill Destination:=Sheets("Dec").Range("a7:a121")
Sheets("Dec").Range("a7:a121").Value = Sheets("Dec").Range("a7:a121").Value
Sheets("Dec").Rows("7:121").Sort Key1:=Sheets("Dec").Range("a7")
End If

'colors start here
If Target.Column < 6 Or Target.Count 1 Then Exit Sub
Select Case Target.Value
Case "Admin"
iClr = 2
Case "C&I - Dual Seat"
iClr = 3
Case "C&I - Typing"
iClr = 3
Case "Engineering"
iClr = 46
Case "Packaging"
iClr = 6
Case "Plant"
iClr = 4
Case "Policy"
iClr = 5
Case "Resale - Exam"
iClr = 8
Case "Resale - Search"
iClr = 8
Case "Resale - Type"
iClr = 8
Case "Single Seat - SL"
iClr = 40
Case "SD - Dual Seat"
iClr = 15
Case "SD - Type"
iClr = 15
Case "SD - Other"
iClr = 15
Case "Order Needs"
iClr = 7
Case Else
iClr = -4142 'no color
End Select

Target.Offset(0, -4).Interior.ColorIndex = iClr

End Sub
--------------------


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile: http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=504175

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default What's wrong with this macro?
if all of the ranges are the same, just on different sheets, i think this is
one way you could shorten the code to make it more manageable


Dim i As Integer
Dim arr As Variant
arr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep",
"Oct", "Nov", "Dec")

For i = LBound(arr) To UBound(arr)
Sheets(arr(i)).Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(" & arr(i) &
"!B7,Summary!B:B,0))"
Sheets(arr(i)).Range("a7").AutoFill
Destination:=Sheets(arr(i)).Range("a7:a121")
Sheets(arr(i)).Range("a7:a121").Value =
Sheets(arr(i)).Range("a7:a121").Value
Sheets(arr(i)).Rows("7:121").Sort Key1:=Sheets(arr(i)).Range("a7")

Next i
End Sub


--


Gary


"belly0fdesire"
wrote in message
news:belly0fdesire.223ve0_1138050005.0906@excelfor um-nospam.com...

Okay. Thank you for the advice. I have included the code below... I
don't know if anyone will be able to help without just downloading the
file. There's nothing to be afraid or worried about. It's a very
impressive spreadsheet that I've designed and I'm pretty proud of it,
but I'm just not very good with VBA code... if anyone could help, it
would be very very appreciated...



Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iClr As Integer

On Error Resume Next

If Target.Count = 1 And (Target.Column = 2 Or Target.Column = 6) Then
Range("A6:O120").Sort Key1:=Range("F6"), Order1:=xlAscending,
Key2:=Range("B6") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

'All months
Sheets("Jan").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Jan!B7,Summary!B:B,0 ))"
Sheets("Jan").Range("a7").AutoFill
Destination:=Sheets("Jan").Range("a7:a121")
Sheets("Jan").Range("a7:a121").Value =
Sheets("Jan").Range("a7:a121").Value
Sheets("Jan").Rows("7:121").Sort Key1:=Sheets("Jan").Range("a7")
Sheets("Feb").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Feb!B7,Summary!B:B,0 ))"
Sheets("Feb").Range("a7").AutoFill
Destination:=Sheets("Feb").Range("a7:a121")
Sheets("Feb").Range("a7:a121").Value =
Sheets("Feb").Range("a7:a121").Value
Sheets("Feb").Rows("7:121").Sort Key1:=Sheets("Feb").Range("a7")
Sheets("Mar").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Mar!B7,Summary!B:B,0 ))"
Sheets("Mar").Range("a7").AutoFill
Destination:=Sheets("Mar").Range("a7:a121")
Sheets("Mar").Range("a7:a121").Value =
Sheets("Mar").Range("a7:a121").Value
Sheets("Mar").Rows("7:121").Sort Key1:=Sheets("Mar").Range("a7")
Sheets("Apr").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Apr!B7,Summary!B:B,0 ))"
Sheets("Apr").Range("a7").AutoFill
Destination:=Sheets("Apr").Range("a7:a121")
Sheets("Apr").Range("a7:a121").Value =
Sheets("Apr").Range("a7:a121").Value
Sheets("Apr").Rows("7:121").Sort Key1:=Sheets("Apr").Range("a7")
Sheets("May").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(May!B7,Summary!B:B,0 ))"
Sheets("May").Range("a7").AutoFill
Destination:=Sheets("May").Range("a7:a121")
Sheets("May").Range("a7:a121").Value =
Sheets("May").Range("a7:a121").Value
Sheets("May").Rows("7:121").Sort Key1:=Sheets("May").Range("a7")
Sheets("Jun").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Jun!B7,Summary!B:B,0 ))"
Sheets("Jun").Range("a7").AutoFill
Destination:=Sheets("Jun").Range("a7:a121")
Sheets("Jun").Range("a7:a121").Value =
Sheets("Jun").Range("a7:a121").Value
Sheets("Jun").Rows("7:121").Sort Key1:=Sheets("Jun").Range("a7")
Sheets("Jul").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Jul!B7,Summary!B:B,0 ))"
Sheets("Jul").Range("a7").AutoFill
Destination:=Sheets("Jul").Range("a7:a121")
Sheets("Jul").Range("a7:a121").Value =
Sheets("Jul").Range("a7:a121").Value
Sheets("Jul").Rows("7:121").Sort Key1:=Sheets("Jul").Range("a7")
Sheets("Aug").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Aug!B7,Summary!B:B,0 ))"
Sheets("Aug").Range("a7").AutoFill
Destination:=Sheets("Aug").Range("a7:a121")
Sheets("Aug").Range("a7:a121").Value =
Sheets("Aug").Range("a7:a121").Value
Sheets("Aug").Rows("7:121").Sort Key1:=Sheets("Aug").Range("a7")
Sheets("Sep").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Sep!B7,Summary!B:B,0 ))"
Sheets("Sep").Range("a7").AutoFill
Destination:=Sheets("Sep").Range("a7:a121")
Sheets("Sep").Range("a7:a121").Value =
Sheets("Sep").Range("a7:a121").Value
Sheets("Sep").Rows("7:121").Sort Key1:=Sheets("Sep").Range("a7")
Sheets("Oct").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Oct!B7,Summary!B:B,0 ))"
Sheets("Oct").Range("a7").AutoFill
Destination:=Sheets("Oct").Range("a7:a121")
Sheets("Oct").Range("a7:a121").Value =
Sheets("Oct").Range("a7:a121").Value
Sheets("Oct").Rows("7:121").Sort Key1:=Sheets("Oct").Range("a7")
Sheets("Nov").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Nov!B7,Summary!B:B,0 ))"
Sheets("Nov").Range("a7").AutoFill
Destination:=Sheets("Nov").Range("a7:a121")
Sheets("Nov").Range("a7:a121").Value =
Sheets("Nov").Range("a7:a121").Value
Sheets("Nov").Rows("7:121").Sort Key1:=Sheets("Nov").Range("a7")
Sheets("Dec").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Dec!B7,Summary!B:B,0 ))"
Sheets("Dec").Range("a7").AutoFill
Destination:=Sheets("Dec").Range("a7:a121")
Sheets("Dec").Range("a7:a121").Value =
Sheets("Dec").Range("a7:a121").Value
Sheets("Dec").Rows("7:121").Sort Key1:=Sheets("Dec").Range("a7")
End If

'colors start here
If Target.Column < 6 Or Target.Count 1 Then Exit Sub
Select Case Target.Value
Case "Admin"
iClr = 2
Case "C&I - Dual Seat"
iClr = 3
Case "C&I - Typing"
iClr = 3
Case "Engineering"
iClr = 46
Case "Packaging"
iClr = 6
Case "Plant"
iClr = 4
Case "Policy"
iClr = 5
Case "Resale - Exam"
iClr = 8
Case "Resale - Search"
iClr = 8
Case "Resale - Type"
iClr = 8
Case "Single Seat - SL"
iClr = 40
Case "SD - Dual Seat"
iClr = 15
Case "SD - Type"
iClr = 15
Case "SD - Other"
iClr = 15
Case "Order Needs"
iClr = 7
Case Else
iClr = -4142 'no color
End Select

Target.Offset(0, -4).Interior.ColorIndex = iClr

End Sub
--------------------


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile:
http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=504175



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default What's wrong with this macro?
watch the word wrap on my prior post, hopefully this will format correctly

Sub test()

Dim i As Integer
Dim arr As Variant
arr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", _
"Oct", "Nov", "Dec")

For i = LBound(arr) To UBound(arr)
Sheets(arr(i)).Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(" & arr(i) & _
"!B7,Summary!B:B,0))"
Sheets(arr(i)).Range("a7").AutoFill _
Destination:=Sheets(arr(i)).Range("a7:a121")
Sheets(arr(i)).Range("a7:a121").Value = Sheets(arr(i)).Range("a7:a121").Value
Sheets(arr(i)).Rows("7:121").Sort Key1:=Sheets(arr(i)).Range("a7")

Next i
End Sub

--


Gary


"belly0fdesire"
wrote in message
news:belly0fdesire.223ve0_1138050005.0906@excelfor um-nospam.com...

Okay. Thank you for the advice. I have included the code below... I
don't know if anyone will be able to help without just downloading the
file. There's nothing to be afraid or worried about. It's a very
impressive spreadsheet that I've designed and I'm pretty proud of it,
but I'm just not very good with VBA code... if anyone could help, it
would be very very appreciated...



Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iClr As Integer

On Error Resume Next

If Target.Count = 1 And (Target.Column = 2 Or Target.Column = 6) Then
Range("A6:O120").Sort Key1:=Range("F6"), Order1:=xlAscending,
Key2:=Range("B6") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

'All months
Sheets("Jan").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Jan!B7,Summary!B:B,0 ))"
Sheets("Jan").Range("a7").AutoFill
Destination:=Sheets("Jan").Range("a7:a121")
Sheets("Jan").Range("a7:a121").Value = Sheets("Jan").Range("a7:a121").Value
Sheets("Jan").Rows("7:121").Sort Key1:=Sheets("Jan").Range("a7")
Sheets("Feb").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Feb!B7,Summary!B:B,0 ))"
Sheets("Feb").Range("a7").AutoFill
Destination:=Sheets("Feb").Range("a7:a121")
Sheets("Feb").Range("a7:a121").Value = Sheets("Feb").Range("a7:a121").Value
Sheets("Feb").Rows("7:121").Sort Key1:=Sheets("Feb").Range("a7")
Sheets("Mar").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Mar!B7,Summary!B:B,0 ))"
Sheets("Mar").Range("a7").AutoFill
Destination:=Sheets("Mar").Range("a7:a121")
Sheets("Mar").Range("a7:a121").Value = Sheets("Mar").Range("a7:a121").Value
Sheets("Mar").Rows("7:121").Sort Key1:=Sheets("Mar").Range("a7")
Sheets("Apr").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Apr!B7,Summary!B:B,0 ))"
Sheets("Apr").Range("a7").AutoFill
Destination:=Sheets("Apr").Range("a7:a121")
Sheets("Apr").Range("a7:a121").Value = Sheets("Apr").Range("a7:a121").Value
Sheets("Apr").Rows("7:121").Sort Key1:=Sheets("Apr").Range("a7")
Sheets("May").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(May!B7,Summary!B:B,0 ))"
Sheets("May").Range("a7").AutoFill
Destination:=Sheets("May").Range("a7:a121")
Sheets("May").Range("a7:a121").Value = Sheets("May").Range("a7:a121").Value
Sheets("May").Rows("7:121").Sort Key1:=Sheets("May").Range("a7")
Sheets("Jun").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Jun!B7,Summary!B:B,0 ))"
Sheets("Jun").Range("a7").AutoFill
Destination:=Sheets("Jun").Range("a7:a121")
Sheets("Jun").Range("a7:a121").Value = Sheets("Jun").Range("a7:a121").Value
Sheets("Jun").Rows("7:121").Sort Key1:=Sheets("Jun").Range("a7")
Sheets("Jul").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Jul!B7,Summary!B:B,0 ))"
Sheets("Jul").Range("a7").AutoFill
Destination:=Sheets("Jul").Range("a7:a121")
Sheets("Jul").Range("a7:a121").Value = Sheets("Jul").Range("a7:a121").Value
Sheets("Jul").Rows("7:121").Sort Key1:=Sheets("Jul").Range("a7")
Sheets("Aug").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Aug!B7,Summary!B:B,0 ))"
Sheets("Aug").Range("a7").AutoFill
Destination:=Sheets("Aug").Range("a7:a121")
Sheets("Aug").Range("a7:a121").Value = Sheets("Aug").Range("a7:a121").Value
Sheets("Aug").Rows("7:121").Sort Key1:=Sheets("Aug").Range("a7")
Sheets("Sep").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Sep!B7,Summary!B:B,0 ))"
Sheets("Sep").Range("a7").AutoFill
Destination:=Sheets("Sep").Range("a7:a121")
Sheets("Sep").Range("a7:a121").Value = Sheets("Sep").Range("a7:a121").Value
Sheets("Sep").Rows("7:121").Sort Key1:=Sheets("Sep").Range("a7")
Sheets("Oct").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Oct!B7,Summary!B:B,0 ))"
Sheets("Oct").Range("a7").AutoFill
Destination:=Sheets("Oct").Range("a7:a121")
Sheets("Oct").Range("a7:a121").Value = Sheets("Oct").Range("a7:a121").Value
Sheets("Oct").Rows("7:121").Sort Key1:=Sheets("Oct").Range("a7")
Sheets("Nov").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Nov!B7,Summary!B:B,0 ))"
Sheets("Nov").Range("a7").AutoFill
Destination:=Sheets("Nov").Range("a7:a121")
Sheets("Nov").Range("a7:a121").Value = Sheets("Nov").Range("a7:a121").Value
Sheets("Nov").Rows("7:121").Sort Key1:=Sheets("Nov").Range("a7")
Sheets("Dec").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Dec!B7,Summary!B:B,0 ))"
Sheets("Dec").Range("a7").AutoFill
Destination:=Sheets("Dec").Range("a7:a121")
Sheets("Dec").Range("a7:a121").Value = Sheets("Dec").Range("a7:a121").Value
Sheets("Dec").Rows("7:121").Sort Key1:=Sheets("Dec").Range("a7")
End If

'colors start here
If Target.Column < 6 Or Target.Count 1 Then Exit Sub
Select Case Target.Value
Case "Admin"
iClr = 2
Case "C&I - Dual Seat"
iClr = 3
Case "C&I - Typing"
iClr = 3
Case "Engineering"
iClr = 46
Case "Packaging"
iClr = 6
Case "Plant"
iClr = 4
Case "Policy"
iClr = 5
Case "Resale - Exam"
iClr = 8
Case "Resale - Search"
iClr = 8
Case "Resale - Type"
iClr = 8
Case "Single Seat - SL"
iClr = 40
Case "SD - Dual Seat"
iClr = 15
Case "SD - Type"
iClr = 15
Case "SD - Other"
iClr = 15
Case "Order Needs"
iClr = 7
Case Else
iClr = -4142 'no color
End Select

Target.Offset(0, -4).Interior.ColorIndex = iClr

End Sub
--------------------


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile:
http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=504175



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default What's wrong with this macro?

Okay... in looking at my macros and formulas, I can understand why I am
not accomplishing my goal.

I'm continuing to try to figure out the code I need in order to make
this workbook sort the way I want it to, but I'm sure I'm going to need
some help. When a change occurs in column B of the "Summary" sheet, I
want the same change to occur on Janec of the months. And then I want
all the sheets to sort accordingly. The way I have the spreadsheet set
up as it is, this will not happen. Please if anyone has time to look at
this and figure out the code I need, it would be extremely appreciated.


Until then, I will continue trying to figure this out on my own and
will post my findings if ever I am successful....... but I doubt I will
be on my own.

Thanks for anyone who is helping me.

If someone who is very proficient with writing VBA script could just
download the spreadsheet from the links I placed above, it would be
EXTREMELY helpful. I understand you're not wanting to trust my links,
but I can assure you that the macro I currently have in the worksheet
will not cause any damage.

Anyway. Any help at all would be very useful. Thank you.


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile: http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=504175

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
What wrong is macro with signcert? Eric Excel Discussion (Misc queries) 4 April 27th 10 11:35 PM
vb macro format is wrong what which is up New Users to Excel 5 February 23rd 10 10:25 PM
What is wrong with this Macro? jeannie v Excel Worksheet Functions 16 February 24th 08 05:05 PM
What have I done wrong with this Macro??? Shandy720[_5_] Excel Programming 1 August 12th 05 04:45 PM
What have I got wrong? (Macro),,, ste mac Excel Programming 2 November 7th 03 11:07 AM


All times are GMT +1. The time now is 02:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement