Reading a color, and setting a value
 

I want to write an excel macro that looks at a cell, matches its color
against a known color value, and then assigns a value to another cell.

The difficulty is knowing how to assess the cell, and knowing what the excel
standard color numbers are.

for example I should be able to use something like:

If aCell.interior.colorindex = 36 then
anotherCell.value = 1
end if

I am just not addressing the cell properly and I also do not know how to
come up with the color index (e.g. I do not know what number red is, what
number cyan is, etc.).

Reading a color, and setting a value
 

Look for
Color Palette -- the 56 Excel Colors
http://www.mvps.org/dmcritchie/excel...s.htm#dpalette

--
HTH

Bob Phillips

"Mark from Princeton" wrote in
message ...
I want to write an excel macro that looks at a cell, matches its color
against a known color value, and then assigns a value to another cell.

The difficulty is knowing how to assess the cell, and knowing what the
excel
standard color numbers are.

for example I should be able to use something like:

If aCell.interior.colorindex = 36 then
anotherCell.value = 1
end if

I am just not addressing the cell properly and I also do not know how to
come up with the color index (e.g. I do not know what number red is, what
number cyan is, etc.).

Reading a color, and setting a value
 

That is a great resource thank you. It answers part of my question.

The other part is how to address the cell properly. the pseudocode I wrote
is what I want to do, but I don't know the best way to implement it.

"Bob Phillips" wrote:

Look for
Color Palette -- the 56 Excel Colors
http://www.mvps.org/dmcritchie/excel...s.htm#dpalette

--
HTH

Bob Phillips

"Mark from Princeton" wrote in
message ...
I want to write an excel macro that looks at a cell, matches its color
against a known color value, and then assigns a value to another cell.

The difficulty is knowing how to assess the cell, and knowing what the
excel
standard color numbers are.

for example I should be able to use something like:

If aCell.interior.colorindex = 36 then
anotherCell.value = 1
end if

I am just not addressing the cell properly and I also do not know how to
come up with the color index (e.g. I do not know what number red is, what
number cyan is, etc.).

Reading a color, and setting a value
 

It is very close to real code. It would just be something like

If Range("B1").interior.colorindex = 36 then
Range("C1").value = 1
end if


--
HTH

Bob Phillips

"Mark from Princeton" wrote in
message ...
That is a great resource thank you. It answers part of my question.

The other part is how to address the cell properly. the pseudocode I
wrote
is what I want to do, but I don't know the best way to implement it.

"Bob Phillips" wrote:

Look for
Color Palette -- the 56 Excel Colors
http://www.mvps.org/dmcritchie/excel...s.htm#dpalette

--
HTH

Bob Phillips

"Mark from Princeton"
wrote in
message ...
I want to write an excel macro that looks at a cell, matches its color
against a known color value, and then assigns a value to another cell.

The difficulty is knowing how to assess the cell, and knowing what the
excel
standard color numbers are.

for example I should be able to use something like:

If aCell.interior.colorindex = 36 then
anotherCell.value = 1
end if

I am just not addressing the cell properly and I also do not know how
to
come up with the color index (e.g. I do not know what number red is,
what
number cyan is, etc.).

Reading a color, and setting a value
 

Thank you very much, that is helpful. I am getting much closer. I am
missing some sort of fundamental concept about ranges. Here is the VB code I
wrote which is syntactically correct but gives me a range of global object
failed

or i = 3 To 6168
If Range("e(i)").Interior.ColorIndex = 35 Then
Range("f(i)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 15 Then
Range("f(i)").Value = 2
ElseIf Range("e(i)").Interior.ColorIndex = 38 Then
Range("f(i)").Value = 3
ElseIf Range("e(i)").Interior.ColorIndex = 4 Then
Range("f(i)").Value = 4
ElseIf Range("e(i)").Interior.ColorIndex = 37 Then
Range("f(i)").Value = 4
End If
Next i
'does the other varialbes
For ii = 3 To 6168
If Range("e(ii)").Interior.ColorIndex = 34 Then 'changover
Range("g(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 6 Then 'PM
Range("h(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 50 Then 'cold startup
Range("i(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 44 Then 'noparts/tooling
Range("j(ii)").Value = 4
ElseIf Range("e(i)").Interior.ColorIndex = 41 Then ' raw materials
Range("k(ii)").Value = 4

ElseIf Range("h(i)").Interior.ColorIndex = 12 Then 'meeting
Range("l(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 42 Then 'met committment
Range("m(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 43 Then 'eng testing
Range("n(ii)").Value = 4
ElseIf Range("e(i)").Interior.ColorIndex = 40 Then 'power out
Range("o(ii)").Value = 4

ElseIf Range("e(i)").Interior.ColorIndex = 7 Then 'holiday/lunch
Range("p(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 8 Then 'line record
Range("q(ii)").Value = 1

End If
Next ii


"Bob Phillips" wrote:

It is very close to real code. It would just be something like

If Range("B1").interior.colorindex = 36 then
Range("C1").value = 1
end if


--
HTH

Bob Phillips

"Mark from Princeton" wrote in
message ...
That is a great resource thank you. It answers part of my question.

The other part is how to address the cell properly. the pseudocode I
wrote
is what I want to do, but I don't know the best way to implement it.

"Bob Phillips" wrote:

Look for
Color Palette -- the 56 Excel Colors
http://www.mvps.org/dmcritchie/excel...s.htm#dpalette

--
HTH

Bob Phillips

"Mark from Princeton"
wrote in
message ...
I want to write an excel macro that looks at a cell, matches its color
against a known color value, and then assigns a value to another cell.

The difficulty is knowing how to assess the cell, and knowing what the
excel
standard color numbers are.

for example I should be able to use something like:

If aCell.interior.colorindex = 36 then
anotherCell.value = 1
end if

I am just not addressing the cell properly and I also do not know how
to
come up with the color index (e.g. I do not know what number red is,
what
number cyan is, etc.).

Reading a color, and setting a value
 

Just a pointer

Not

If Range("e(i)").Interior.ColorIndex = 35 Then

but

If Range("e" & i).Interior.ColorIndex = 35 Then

as the address is a string.

--
HTH

Bob Phillips

"Mark from Princeton" wrote in
message ...
Thank you very much, that is helpful. I am getting much closer. I am
missing some sort of fundamental concept about ranges. Here is the VB
code I
wrote which is syntactically correct but gives me a range of global object
failed

or i = 3 To 6168
If Range("e(i)").Interior.ColorIndex = 35 Then
Range("f(i)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 15 Then
Range("f(i)").Value = 2
ElseIf Range("e(i)").Interior.ColorIndex = 38 Then
Range("f(i)").Value = 3
ElseIf Range("e(i)").Interior.ColorIndex = 4 Then
Range("f(i)").Value = 4
ElseIf Range("e(i)").Interior.ColorIndex = 37 Then
Range("f(i)").Value = 4
End If
Next i
'does the other varialbes
For ii = 3 To 6168
If Range("e(ii)").Interior.ColorIndex = 34 Then 'changover
Range("g(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 6 Then 'PM
Range("h(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 50 Then 'cold startup
Range("i(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 44 Then 'noparts/tooling
Range("j(ii)").Value = 4
ElseIf Range("e(i)").Interior.ColorIndex = 41 Then ' raw materials
Range("k(ii)").Value = 4

ElseIf Range("h(i)").Interior.ColorIndex = 12 Then 'meeting
Range("l(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 42 Then 'met committment
Range("m(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 43 Then 'eng testing
Range("n(ii)").Value = 4
ElseIf Range("e(i)").Interior.ColorIndex = 40 Then 'power out
Range("o(ii)").Value = 4

ElseIf Range("e(i)").Interior.ColorIndex = 7 Then 'holiday/lunch
Range("p(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 8 Then 'line record
Range("q(ii)").Value = 1

End If
Next ii


"Bob Phillips" wrote:

It is very close to real code. It would just be something like

If Range("B1").interior.colorindex = 36 then
Range("C1").value = 1
end if


--
HTH

Bob Phillips

"Mark from Princeton"
wrote in
message ...
That is a great resource thank you. It answers part of my question.

The other part is how to address the cell properly. the pseudocode I
wrote
is what I want to do, but I don't know the best way to implement it.

"Bob Phillips" wrote:

Look for
Color Palette -- the 56 Excel Colors
http://www.mvps.org/dmcritchie/excel...s.htm#dpalette

--
HTH

Bob Phillips

"Mark from Princeton"
wrote in
message ...
I want to write an excel macro that looks at a cell, matches its
color
against a known color value, and then assigns a value to another
cell.

The difficulty is knowing how to assess the cell, and knowing what
the
excel
standard color numbers are.

for example I should be able to use something like:

If aCell.interior.colorindex = 36 then
anotherCell.value = 1
end if

I am just not addressing the cell properly and I also do not know
how
to
come up with the color index (e.g. I do not know what number red
is,
what
number cyan is, etc.).

Reading a color, and setting a value
 

Thank you so much Mr. Phillips, that appears to have done the trick!

"Bob Phillips" wrote:

Just a pointer

Not

If Range("e(i)").Interior.ColorIndex = 35 Then

but

If Range("e" & i).Interior.ColorIndex = 35 Then

as the address is a string.

--
HTH

Bob Phillips

"Mark from Princeton" wrote in
message ...
Thank you very much, that is helpful. I am getting much closer. I am
missing some sort of fundamental concept about ranges. Here is the VB
code I
wrote which is syntactically correct but gives me a range of global object
failed

or i = 3 To 6168
If Range("e(i)").Interior.ColorIndex = 35 Then
Range("f(i)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 15 Then
Range("f(i)").Value = 2
ElseIf Range("e(i)").Interior.ColorIndex = 38 Then
Range("f(i)").Value = 3
ElseIf Range("e(i)").Interior.ColorIndex = 4 Then
Range("f(i)").Value = 4
ElseIf Range("e(i)").Interior.ColorIndex = 37 Then
Range("f(i)").Value = 4
End If
Next i
'does the other varialbes
For ii = 3 To 6168
If Range("e(ii)").Interior.ColorIndex = 34 Then 'changover
Range("g(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 6 Then 'PM
Range("h(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 50 Then 'cold startup
Range("i(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 44 Then 'noparts/tooling
Range("j(ii)").Value = 4
ElseIf Range("e(i)").Interior.ColorIndex = 41 Then ' raw materials
Range("k(ii)").Value = 4

ElseIf Range("h(i)").Interior.ColorIndex = 12 Then 'meeting
Range("l(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 42 Then 'met committment
Range("m(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 43 Then 'eng testing
Range("n(ii)").Value = 4
ElseIf Range("e(i)").Interior.ColorIndex = 40 Then 'power out
Range("o(ii)").Value = 4

ElseIf Range("e(i)").Interior.ColorIndex = 7 Then 'holiday/lunch
Range("p(ii)").Value = 1
ElseIf Range("e(i)").Interior.ColorIndex = 8 Then 'line record
Range("q(ii)").Value = 1

End If
Next ii


"Bob Phillips" wrote:

It is very close to real code. It would just be something like

If Range("B1").interior.colorindex = 36 then
Range("C1").value = 1
end if


--
HTH

Bob Phillips

"Mark from Princeton"
wrote in
message ...
That is a great resource thank you. It answers part of my question.

The other part is how to address the cell properly. the pseudocode I
wrote
is what I want to do, but I don't know the best way to implement it.

"Bob Phillips" wrote:

Look for
Color Palette -- the 56 Excel Colors
http://www.mvps.org/dmcritchie/excel...s.htm#dpalette

--
HTH

Bob Phillips

"Mark from Princeton"
wrote in
message ...
I want to write an excel macro that looks at a cell, matches its
color
against a known color value, and then assigns a value to another
cell.

The difficulty is knowing how to assess the cell, and knowing what
the
excel
standard color numbers are.

for example I should be able to use something like:

If aCell.interior.colorindex = 36 then
anotherCell.value = 1
end if

I am just not addressing the cell properly and I also do not know
how
to
come up with the color index (e.g. I do not know what number red
is,
what
number cyan is, etc.).