| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.). |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.). |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.). |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.). |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.). |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Setting Wall Color in 3-D charts | Charts and Charting in Excel | |||
| Reading RGB background color from cell | Excel Programming | |||
| Automatic color setting in Excel | Excel Discussion (Misc queries) | |||
| Setting Color of PIE sections | Charts and Charting in Excel | |||
| setting cell color | Excel Worksheet Functions | |||
Hybrid Mode
