Coloring cells automatically

lalcanta

New Member
Joined
Sep 27, 2002
Messages
11
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fryer Tuck

Board Regular
Joined
Mar 20, 2002
Messages
64
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

New Member
Joined
Sep 27, 2002
Messages
11
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

Active Member
Joined
Aug 21, 2002
Messages
255
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

New Member
Joined
Sep 27, 2002
Messages
11

ADVERTISEMENT

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.
 

lalcanta

New Member
Joined
Sep 27, 2002
Messages
11

ADVERTISEMENT

Great!!!
It's working!!!

Thank you all for the help...

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

Regards.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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.

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

Regards!

Yogi
 

lalcanta

New Member
Joined
Sep 27, 2002
Messages
11
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.
 

Forum statistics

Threads
1,148,282
Messages
5,745,836
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top