Coloring cells automatically

[lalcanta]

Hi All,
I have a workbook with 2 spreadsheets, the first one contain among other 6 columns a column labeled “STATION”, the second spreadsheet is formatted to print “ID Tags” (this is multi-cell) with all kinds of information merged from different columns and rows from the first sheet.

Each cell on the “STATION” column gets a background color for ID purposes, but I have to manually edit the “TAGs” sheet every time…

Is there a way that when I select the background color for a cell in the “STATION” column, the color ID cell on the “TAGs” sheet is automatically selected?

I.E If a select a yellow background for the first sheet, cell G7, then the cell A1 on the second sheet becomes yellow.

I’m talking about the background, not the text, and coloring the text won’t work, because most printers are B &W.

Best Regards.

 

[Fryer Tuck]

Here is some code that will work, but you may want to limit it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Intg As Integer
Intg = ActiveCell.Interior.ColorIndex
Tags.Select
ActiveCell.Range("A1:C3").Interior.ColorIndex = Intg
End Sub


Explanation: Put this in your STATION spreadsheet. Go to your TAGS spreadsheet and select the first cell you want colored. Now go to your station sheet and select the station. The program will go to the TAGS sheet and give the same color to the area specified. (In the program above, the first 3 rows and cells combination from the cell you origianlly selected when you were on the TAGS sheet.

You may want to limit this to an if statment that checks if the "Target" is in row "G" (or whatever row you have the color in in the STATION sheet; since these replys don't show the original meesage I am responding to, I can't tell if you said column "G" or some other column.)

Hope this helps.
F.T.

 

[lalcanta]

Hello,
thank you for your answer...

I fired up VB editor copy and paste into the Staion sheet and gave me and error...

I Pastes special, no luck..

I pasted into the cell "G1" and "puts" all the text into the "TAGS A1:XX"...

no matter what I do I can get it to work...

Any suggestions, maybe I'm doing some wrong?


Regards.

 

[rikrak]

Hi,

What F.T. is saying:
1) Open the VB editor (Alt F11)
2) Hit Ctrl+R to ensure that the Project Explorer is visible
3) In there you should be able to see your workbook saying something like VBAProject (Workbookname.xls)
4) Double click on the STATION icon
5) The code module opens up
6) Paste the code into it....

 

[lalcanta]

Hi,
That's exactly what I'm doing as you can see in the code below..

But is givin me an error and if I select debug, the "Tags.Select" is highlited in yellow.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Intg As Integer
Intg = ActiveCell.Interior.ColorIndex
Tags.Select
ActiveCell.Range("A1:C3").Interior.ColorIndex = Intg
End Sub

Regards.

 

[Dragracer]

Change Tags.Select
To Sheets("Tags").Select

 

[lalcanta]

Great!!!
It's working!!!

Thank you all for the help...

you just saved me hours an hours of work!!!

Regards.

 

[Yogi Anand]

Hi lalcanta:

If I have understood you correctly, you want to have the background color of cell A1 in sheet2 to be the same as the background color of cell G1 in sheet1 -- if this is the case then the following code in sheet1 would do it.

Private Sub Worksheet_Activate()
[sheet2!A1].Interior.ColorIndex = [sheet1!G7].Interior.ColorIndex
End Sub

Regards!

Yogi

 

[lalcanta]

Yogi and All,
thanks for the great help, the last answer is exactly what i was looking for....


This forum is the VERY BEST!!! I've ever found.


Thank you.