Excel Cell Conditioning based on a value in cell

[monnina]

Hi,

I'm sorry if this question has already been asked but I have looked through and can't see it.

I am creating an excel sheet for the men on my factory floor.

There are drop down list where they can pick if a task is either "Complete" "Complete (Issue)" "In Stock" "Incomplete" or "Incomplete (Issue)"

They have requested that if they pick a task as "Incomplete" or "Incomplete (Issue)" Then the whole row should become red so it is extremely obvious that they do not proceed to the next task (There are various coloums within the row with these tasks and over 50 tasks)
It has been very time consuming doing this using the conditioning formating option. Is there a VBA code i could use for this?

In the conditional format i used this information:

=$B$6="Incomplete"
I picked the format red
applies to:
=$C$6:$AD$6

I would also like that if for example the painter picked a "location" for one of his finished products that the previous locations in that row would now read "moved on"


Thanks so much.

 

[vaskov17]

The code below will color cell from C to AD in red if the value chosen in B contains the word "Incomplete". Put it in the VBA section for the sheet you want to run it in.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And InStr(1, Target.Text, "Incomplete", vbTextCompare) > 0 Then
        With Range("C" & Target.Row, "AD" & Target.Row).Interior
            .Color = 255
            .Pattern = xlSolid
        End With
    Else
        Range("C" & Target.Row, "AD" & Target.Row).Interior.Pattern = xlNone
    End If
End Sub

For the second part of your question regarding "location" and "moved on", I need more detailed information on where everything is.

 

[monnina]

Thanks so much!! It works really well in column B, How do I then apply this formula for the incomplete tasks in colums F, G and J?

When "Fitting Shop" , " Paint Shop" , "Welding Shop" , "Yard" or "Moved On" is selected in colum "N" the corresponding cell in "I" should change to read "Moved on". Similarly if colum "P" reads any of the above column N should read moved on and If "U" reads then above then "P" changes to moved on and if the "W" reads the above then "U" should read moved on.

let me know if you need a better explanation.

Thanks

 

[vaskov17]

try this for the first part:

Private Sub Worksheet_Change(ByVal Target As Range)
    '2 is Column B, 6 is Column F, 7 is Column G, 10 is Column J
    If Target.Column = 2 Or Target.Column = 6 Or Target.Column = 7 Or Target.Column = 10 Then
        If InStr(1, Target.Text, "Incomplete", vbTextCompare) > 0 Then
            With Range("C" & Target.Row, "AD" & Target.Row).Interior
                .Color = 255
                .Pattern = xlSolid
            End With
        Else
            Range("C" & Target.Row, "AD" & Target.Row).Interior.Pattern = xlNone
        End If
    End If
End Sub

 

[monnina]

That works perfectly thanks so much!!

 

[vaskov17]

there are some issues that I just noticed and will post something to address them a little later

 

[monnina]

Have you had any more thoughts on the "moved on" function?

 

[vaskov17]

try this for both parts of your problem:

Private Sub Worksheet_Change(ByVal Target As Range)
    '2 is Column B, 6 is Column F, 7 is Column G, 10 is Column J
    If Target.Column = 2 Or Target.Column = 6 Or Target.Column = 7 Or Target.Column = 10 Then
        If InStr(1, Target.Value2, "Incomplete", vbTextCompare) > 0 Then
            With Range("C" & Target.Row, "AD" & Target.Row).Interior
                .Color = 255
                .Pattern = xlSolid
            End With
        Else
            If InStr(1, Range("B" & Target.Row).Value2, "Incomplete", vbTextCompare) > 0 Then
            ElseIf InStr(1, Range("F" & Target.Row).Value2, "Incomplete", vbTextCompare) > 0 Then
            ElseIf InStr(1, Range("G" & Target.Row).Value2, "Incomplete", vbTextCompare) > 0 Then
            ElseIf InStr(1, Range("J" & Target.Row).Value2, "Incomplete", vbTextCompare) > 0 Then
            Else
                Range("C" & Target.Row, "AD" & Target.Row).Interior.Pattern = xlNone
            End If
        End If
    ElseIf (LCase(Target.Value2) = "fitting shop" Or LCase(Target.Value2) = "welding shop" Or LCase(Target.Value2) = "paint shop" Or LCase(Target.Value2) = "yard" Or LCase(Target.Value2) = "moved on") Then
        Select Case Target.Column
            Case 14: Range("I" & Target.Row) = "Moved On"
            Case 16: Range("N" & Target.Row) = "Moved On"
            Case 21: Range("P" & Target.Row) = "Moved On"
            Case 22: Range("U" & Target.Row) = "Moved On"
        End Select
    End If
End Sub