Resistor color code-ing in worksheet

[mnmhenry]

Hi all. I want to creat a sheet that will hel pe print some labes for my resistor (electronic componants) cabinet.

resistors are marked with colors to determin the value of the resistior

ie
black = 0
brown = 1
Red = 2

...and it goes on

I want to insert lets say "1" into cell A1 and via R1C1 method, cell A2 to change to brown...

put a "2" into cell B1 and similar again B2 turns red. All the colors are pretty well handeld by excel so if I can get a handle on the first three colors and formuli, I should be able to continue from there....

R1C1 has to be use because im pasting these values down the page to print my labels.


Thanks for any help
Mark

 

[Derek]

Hi there

This might give you some ideas:

Create a table of colors. (Here I have used G1:G3). Color each cell and type in its number (eg G1 = 0 and is black, G2 = 1 and is brown etc)

Alt+F11 to go to the VB Editor and paste this code in the large code window:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Range("G1:G3")
If cell.Value = Target.Value Then
Target.Offset(1, 0).Interior.ColorIndex = cell.Interior.ColorIndex
Exit Sub
End If
Next
End Sub

Whereever you type in a number it will trigger this macro to put the appropriate color in the cell below it.

regards
Derek

 

[Ziggy12]

Not sure there is a formula driven method of colouring cells (or the other way return a value for a specific colour which I think would be handy). A macro could do it (if range("A1")= 2 then range("B2").interiorcolor=2 or whatever)

Think the only way to do would be to use conditional formatting in the format menu.