Hi all,
I got this worksheet that keeps track of returns.
Different items of a item can be returned (retour 1 and retour 2)
When an x is filled in behind the item in Retour (column B) the item should be colored red
When an x is filled in behind the in Returned (column C) the item should be colored black
This works.
But when 2 items are returned it doesn't work anymore.
When retour 2 is filled before retour 1 the item is black when i still miss the x in column C (returned)
Looks like the code is just going from top to bottom and doesn't look back
When retour 2 is complete it can happen that retour 1 isn't and then the item name should be still colored red.
I got this worksheet that keeps track of returns.
Different items of a item can be returned (retour 1 and retour 2)
When an x is filled in behind the item in Retour (column B) the item should be colored red
When an x is filled in behind the in Returned (column C) the item should be colored black
This works.
But when 2 items are returned it doesn't work anymore.
When retour 2 is filled before retour 1 the item is black when i still miss the x in column C (returned)
Looks like the code is just going from top to bottom and doesn't look back
When retour 2 is complete it can happen that retour 1 isn't and then the item name should be still colored red.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Intersect(Target, Range("B:G")) Is Nothing Then Exit Sub
i = Target.Row
Select Case LCase(Range("B" & i).Value)
Case "x"
Range("A" & i).Font.color = RGB(255, 0, 0)
Case ""
Range("A" & i).Font.color = RGB(0, 0, 0)
End Select
Select Case LCase(Range("C" & i).Value)
Case "x"
Range("A" & i).Font.color = RGB(0, 0, 0)
End Select
Select Case LCase(Range("F" & i).Value)
Case "x"
Range("A" & i).Font.color = RGB(255, 0, 0)
Case "F"
Range("A" & i).Font.color = RGB(255, 0, 0)
End Select
Select Case LCase(Range("G" & i).Value)
Case "x"
Range("A" & i).Font.color = RGB(0, 0, 0)
End Select
End Sub
Book1 | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Retour 1 | Retour 2 | ||||||||||||||
2 | Item name | Retour | Returned | Retour | Returned | |||||||||||
3 | Screwdriver | x | x | |||||||||||||
4 | Batch | x | ||||||||||||||
5 | Tree | x | x | x | x | |||||||||||
6 | Grass | x | x | x | This one doesn't work. Retour 2 is complete but retour 1 not | |||||||||||
Sheet1 |