roninblackman
New Member
- Joined
- Sep 5, 2014
- Messages
- 3
Hello,
I am a first time poster and I was hoping someone could provide me with a little help. I'm running into an error when I run the macro I've created. Before I post the code, I would like to explain what it is I am attempting. I've got a console that pulls in various extracts from other sources and said data into a few tabs on my worksheet. It then generates a pivot table that puts the data into an organized view. The data itself consists of Project Managers, Resources, Project Names, etc. The macro works like a charm and I'm not having any issue currently with that portion. The pivot table separates the data into projects and then it lists the resources attached to that project. What I'm trying to add is a section of code that filters through the data and finds the project manager associated to that project and then highlights that resource in yellow for a quick visual indicator.
Sounds easy, right? Well.. I'm stuck and every time I try to run the code it locks up excel and for the life of me I cannot figure it out. I've attached the section of code that I've worked on to highlight the Project Manager. Could someone please provide me some assistance in troubleshooting this? I'll be more than happy to give any further information if that would help. Any help regarding this issue would be greatly appreciated. Thank you!
This portion is highlighted when I debug..
If PivotProjectName = Sheet8.Cells(Count, "C").Value Then
I am a first time poster and I was hoping someone could provide me with a little help. I'm running into an error when I run the macro I've created. Before I post the code, I would like to explain what it is I am attempting. I've got a console that pulls in various extracts from other sources and said data into a few tabs on my worksheet. It then generates a pivot table that puts the data into an organized view. The data itself consists of Project Managers, Resources, Project Names, etc. The macro works like a charm and I'm not having any issue currently with that portion. The pivot table separates the data into projects and then it lists the resources attached to that project. What I'm trying to add is a section of code that filters through the data and finds the project manager associated to that project and then highlights that resource in yellow for a quick visual indicator.
Sounds easy, right? Well.. I'm stuck and every time I try to run the code it locks up excel and for the life of me I cannot figure it out. I've attached the section of code that I've worked on to highlight the Project Manager. Could someone please provide me some assistance in troubleshooting this? I'll be more than happy to give any further information if that would help. Any help regarding this issue would be greatly appreciated. Thank you!
Code:
' To assign color to project manager in the pivot by project tab
Dim DataPMname As String
Dim isFound As Boolean
Dim PivotProjectName As String
Dim Count As Long
Dim DataSheetLastRow As Long
isFound = False
PivotProjectName = ""
Count = 1
LastDataRow = Sheet9.UsedRange.Rows(Sheet9.UsedRange.Rows.Count).Row
DataSheetLastRow = Sheet8.UsedRange.Rows(Sheet8.UsedRange.Rows.Count).Row
For r = 1 To LastDataRow
PivotProjectName = Sheet9.Cells(r, "A").Value
While isFound = False And PivotProjectName <> ""
For x = 1 To DataSheetLastRow
If x = DataSheetLastRow Then
isFound = True
End If
If PivotProjectName = Sheet8.Cells(Count, "C").Value Then
DataPMname = UCase(Sheet8.Cells(Count, "D").Value)
isFound = True
End If
Next
isFound = False
Wend
If Sheet9.Cells(r, "B").Value = DataPMname Then
Sheet9.Cells(r, "B").Interior.Color = RGB(255, 255, 0)
Else
Sheet9.Cells(r, "B").Interrior.Color = RGB(255, 255, 255)
End If
Next
Application.ScreenUpdating = True
End Function
This portion is highlighted when I debug..
If PivotProjectName = Sheet8.Cells(Count, "C").Value Then
Last edited by a moderator: