Solved: CONDITIONNAL FORMAT OR MACRO

[guatelize]

:banghead: :dunno :help
I have looking on the internet for several days, and can't find any solution.
Attached file shows 2 sheets, 1st is where the color-references are indicated with criteria. In sheet2, orders are placed with their delivery status, if ColA in sheet2 is modified with a letter from sheet 1, I wish the same color to be displayed for the delivery status .

Thanks for your help: pray2: :thumb
Oliver

[jonhaus]

Try putting attaching this code to your orders sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then

Dim x As Integer
ReDim Colors(10, 2)
For x = LBound(Colors) To UBound(Colors)
Colors(x, 1) = Sheets("References").Cells(x + 1, 4).Value
Colors(x, 2) = Sheets("References").Cells(x + 1, 1).Interior.Color
Next x

For x = LBound(Colors) To UBound(Colors)
If Target.Value = Colors(x, 1) Then
Rows(Target.Row).Interior.Color = Colors(x, 2)
End If
Next x

End If

End Sub

Good Luck,
-jonhaus
For more Excel and VBA help visit my website (jonhaus.hubpages.com)

[jonhaus]

I forgot to set the upperbound limit of the array as a variable.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then

Dim x As Integer
Dim lastrow As Integer

lastrow = Sheets("References").Range("D" & Rows.Count).End(xlUp).Row

ReDim Colors(lastrow, 2)
For x = LBound(Colors) To UBound(Colors)
Colors(x, 1) = Sheets("References").Cells(x + 1, 4).Value
Colors(x, 2) = Sheets("References").Cells(x + 1, 1).Interior.Color
Next x

For x = LBound(Colors) To UBound(Colors)
If Target.Value = Colors(x, 1) Then
Rows(Target.Row).Interior.Color = Colors(x, 2)
End If
Next x

End If

End Sub

[guatelize]

Suuuuuuper, works perfect, thanks for the fast reply.
Just have one other questions : is it possiblle not to color the whole row only until Col Z instead of the end of the row & to have borders around the coloured cells.
Thanks again.

[CatDaddy]

For x = LBound(Colors) To UBound(Colors)
If Target.Value = Colors(x, 1) Then
Range("A" & Target.Row ":Z" & Target.Row).Interior.Color = Colors(x, 2)
End If
Next x

[guatelize]

Thanks again for your fast reply. I tried to resolve the error problem by myself, but in vain. No luck.
The error keeps harassing me.:bug: :banghead:

[jonhaus]

It looks like you were missing and ampersand, try this.

For x = LBound(Colors) To UBound(Colors)
If Target.Value = Colors(x, 1) Then
Range("A" & Target.Row & ":Z" & Target.Row).Interior.Color = Colors(x, 2)
End If
Next x

[guatelize]

Wonderful, thank you very much, works perfectly. Good luck with everything.
Oliver

[guatelize]

Just a little detail : every time I select & delete in sheet2 ColumnA the macro returns an error ?:doh: