Colour macro
 

Afternoon

I have my data that I want to review in column H and depending on the data
in that coloumn colour the entire row (columns A to J).

An example of a code is;
ZL049 which I want to be Yellow
EZ006 which I want to be Green
EY001 which I want to be purple

How do I write and where?

Many thanks
Pete

Colour macro
 

Without code (that is without VBA) use Condiitonal formatting with Formula Is.

Using code:

Sub LittlePete()
v1 = "ZL049"
v2 = "EZ006"
v3 = "EY001"
n = Cells(Rows.Count, "J").End(xlUp).Row
Set rr = Range("J1:J" & n)
yellow = 6
green = 10
purple = 13
For Each r In rr
v = r.Value
If v = v1 Then r.EntireRow.Interior.ColorIndex = yellow
If v = v2 Then r.EntireRow.Interior.ColorIndex = green
If v = v3 Then r.EntireRow.Interior.ColorIndex = purple
Next
End Sub
--
Gary''s Student - gsnu200793


"LITTLE PETE" wrote:

Afternoon

I have my data that I want to review in column H and depending on the data
in that coloumn colour the entire row (columns A to J).

An example of a code is;
ZL049 which I want to be Yellow
EZ006 which I want to be Green
EY001 which I want to be purple

How do I write and where?

Many thanks
Pete

Colour macro
 

Thanks Gary

If the code changes from being alpha/numeric (ZL049) to just numeric like
"19" will anything need to change? I have looked at the data and using just
numbers will be a lot easier and faster.

Also were is it best to keep this, on the sheet or as a module?
Thanks Peter



"Gary''s Student" wrote:

Without code (that is without VBA) use Condiitonal formatting with Formula Is.

Using code:

Sub LittlePete()
v1 = "ZL049"
v2 = "EZ006"
v3 = "EY001"
n = Cells(Rows.Count, "J").End(xlUp).Row
Set rr = Range("J1:J" & n)
yellow = 6
green = 10
purple = 13
For Each r In rr
v = r.Value
If v = v1 Then r.EntireRow.Interior.ColorIndex = yellow
If v = v2 Then r.EntireRow.Interior.ColorIndex = green
If v = v3 Then r.EntireRow.Interior.ColorIndex = purple
Next
End Sub
--
Gary''s Student - gsnu200793


"LITTLE PETE" wrote:

Afternoon

I have my data that I want to review in column H and depending on the data
in that coloumn colour the entire row (columns A to J).

An example of a code is;
ZL049 which I want to be Yellow
EZ006 which I want to be Green
EY001 which I want to be purple

How do I write and where?

Many thanks
Pete

Colour macro
 

I like to keep macros in a module.

Changing from alpha to numeric is something like:

v1=19
--
Gary''s Student - gsnu200793


"LITTLE PETE" wrote:

Thanks Gary

If the code changes from being alpha/numeric (ZL049) to just numeric like
"19" will anything need to change? I have looked at the data and using just
numbers will be a lot easier and faster.

Also were is it best to keep this, on the sheet or as a module?
Thanks Peter



"Gary''s Student" wrote:

Without code (that is without VBA) use Condiitonal formatting with Formula Is.

Using code:

Sub LittlePete()
v1 = "ZL049"
v2 = "EZ006"
v3 = "EY001"
n = Cells(Rows.Count, "J").End(xlUp).Row
Set rr = Range("J1:J" & n)
yellow = 6
green = 10
purple = 13
For Each r In rr
v = r.Value
If v = v1 Then r.EntireRow.Interior.ColorIndex = yellow
If v = v2 Then r.EntireRow.Interior.ColorIndex = green
If v = v3 Then r.EntireRow.Interior.ColorIndex = purple
Next
End Sub
--
Gary''s Student - gsnu200793


"LITTLE PETE" wrote:

Afternoon

I have my data that I want to review in column H and depending on the data
in that coloumn colour the entire row (columns A to J).

An example of a code is;
ZL049 which I want to be Yellow
EZ006 which I want to be Green
EY001 which I want to be purple

How do I write and where?

Many thanks
Pete