Select case with multiple case

[Bandito1]

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.

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

 

[jasonb75]

Because all cases are changing the font colour of column A you need to have them in order of descending priority. i.e. lowest priority first, highest priority last.

 

[Bandito1]

Well, there is no priority.
It doesn't matter if retour 2 is first or last.

Guess select case is not the solution then?

 

[Micron]

A Select Case block exits as soon as any test is true. Your only Select Case option is probably to test for True and write a case statement for each possibility.

Select Case True
  Case A = 1 and B = "" and C = ""
 
  Case A = 1 and B = 1 and C = ""

and so on

I don't think I'd write Select Case blocks for one case. Maybe IF blocks would be better.

 

[jasonb75]

I think I misread it slightly first time. Try this slight change to your code, if I'm following what you want correctly now then it will do what you need it to.

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)
    Case Else
        Exit Sub
    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

 

[Micron]

Odd, I get a different result and I made no changes as far as I can tell.

Tree	x	x			x	x
Grass	x				x	x

EDIT - OK, I think that's because I was stepping through and likely it went red and then it was undone after. Can't be certain since I have to edit my post before the time runs out.

 

[Micron]

I must not be understanding the requirements because to me, it looks to be as simple as

If Range("B" & i) = "x" And Range("C" & i) = "x" Then
   Range("A" & i).Font.Color = RGB(0, 0, 0)
Else
   Range("A" & i).Font.Color = RGB(255, 0, 0)
End If

 

[jasonb75]

I must not be understanding the requirements

I read it wrong first time, as far as I can see (which is what I based my quick edit to the original code on).

If either pair in the target row (B = "x" And C = "") Or (F = "x" And G = "") then red font. Anything else, black font).

It could be simplified significantly but I stuck with the OP's method so that they could see the difference and maybe understand it easier. The code is simple enough that it is not going to make a difference in efficiency.

 

[Bandito1]

Sorry for my late reply.

Works like a charm, thank you !

 

[bebo021999]

Alternative solution with different approach.
"If both "Returned" are "x", black color
else, if any C,F,G is "x", black color"
Therefore, I came up with:
* Set defaul color as red
* Use the if statement to change red to black

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B:G")) Is Nothing Then Exit Sub
With Cells(Target.Row, 1)
    .Font.Color = vbRed
    If .Offset(, 2) = "x" And .Offset(, 6) = "x" Then
        .Font.Color = vbBlack
        Exit Sub
    ElseIf .Offset(, 2) <> "" Or .Offset(, 5) <> "" Or .Offset(, 6) <> "" Then .Font.Color = vbBlack
    End If
End With
End Sub