SewerUrchin
New Member
- Joined
- Jan 25, 2005
- Messages
- 24
Hi folks:
Sorry this is longish . . . A workbook that allows a distributed team to report weekly status via a spreadsheet in the same. Conditional formatting is used as much as it can be. In one case VB is needed.
Each sheet has a task in each row, dedicated to reporting task status. There are 5 possible values for the status field. They are : Red, Yellow, Green, Done & Unused. If status is yellow or red, then two new columns have to be filled in: Impact to team and Action Plan. Here is where the quandary is found . . .
If the status goes from red or yellow, to any value other than red or yellow, the Impact to team and Action Plan fields should be cleared for that row. (conditional formatting cross hatches it). The problem: the code blanks everything, all the time, not just when the row status value is something other than red or yellow. I can’t figure out why though.
Here is a sample of the code I’ve used (I’m a novice so please if you know of a way to shorten the code so it iterates rather than having to list code for each row, I’m way open to it):
Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng1 As Range
On Error Resume Next
If Sheets("PI").Range("F6").Value = "Green" Or "Done" Or "Unused" Then
Set Rng1 = Sheets("PI").Range("G6,H6")
Rng1.ClearContents
Else
End If
If Sheets("PI").Range("F7").Value = "Green" Or "Done" Or "Unused" Then
Set Rng1 = Sheets("PI").Range("G7,H7")
Rng1.ClearContents
Else
End If
This code is repeated for each row (a total of 20 of them). I’ve used Rng1 over because it would seem that once you are past the preceding block, remapping the range wouldn’t be an issue . . . But maybe it is. As I said, I’m a very novice user of VB.
My question is: Why does this code clear the Impact to team and Action Plan cells all the time, instead of only doing it when the value in column F is something other than red/yellow?
Your help is very much appreciated!
SU
Sorry this is longish . . . A workbook that allows a distributed team to report weekly status via a spreadsheet in the same. Conditional formatting is used as much as it can be. In one case VB is needed.
Each sheet has a task in each row, dedicated to reporting task status. There are 5 possible values for the status field. They are : Red, Yellow, Green, Done & Unused. If status is yellow or red, then two new columns have to be filled in: Impact to team and Action Plan. Here is where the quandary is found . . .
If the status goes from red or yellow, to any value other than red or yellow, the Impact to team and Action Plan fields should be cleared for that row. (conditional formatting cross hatches it). The problem: the code blanks everything, all the time, not just when the row status value is something other than red or yellow. I can’t figure out why though.
Here is a sample of the code I’ve used (I’m a novice so please if you know of a way to shorten the code so it iterates rather than having to list code for each row, I’m way open to it):
Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng1 As Range
On Error Resume Next
If Sheets("PI").Range("F6").Value = "Green" Or "Done" Or "Unused" Then
Set Rng1 = Sheets("PI").Range("G6,H6")
Rng1.ClearContents
Else
End If
If Sheets("PI").Range("F7").Value = "Green" Or "Done" Or "Unused" Then
Set Rng1 = Sheets("PI").Range("G7,H7")
Rng1.ClearContents
Else
End If
This code is repeated for each row (a total of 20 of them). I’ve used Rng1 over because it would seem that once you are past the preceding block, remapping the range wouldn’t be an issue . . . But maybe it is. As I said, I’m a very novice user of VB.
My question is: Why does this code clear the Impact to team and Action Plan cells all the time, instead of only doing it when the value in column F is something other than red/yellow?
Your help is very much appreciated!
SU
