linking cell shading across worksheets

adrock336

New Member
Joined
Jun 17, 2008
Messages
3
I have a macro that shades cells a certain color based on a specific value. The macro works fine but when I try to link that cell with a cell on another sheet only the value comes across not the cell color.

What am I missing? Is it the macro or is there something not set correctly on the worksheet?


Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)
Dim Position As String
Dim vColor As Integer
Dim cell As Range
For Each cell In target
Model = cell.Value
vColor = 0 'default is no color
Select Case Model
Case "120D"
vColor = 45
Case "160D"
vColor = 43
Case "200DL"
vColor = 42
Case "240DL"
vColor = 40
Case "120Z"
vColor = 38
Case "160Z"
vColor = 48
Case "200Z"
vColor = 47
Case "240Z"
vColor = 50
Case "270Z"
vColor = 39
End Select
Application.EnableEvents = False
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True
Next cell
End Sub

Thanks in advance!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi, adrock336,
WELCOME to the BOARD!!!!!

Your code is changing the background of the cells of the worksheet where the code resides.
Please explain what you need. Or would copying the code to the other sheet modules do what you want?
(if you need it for all sheets, then it can be put in the workbook module with some little changes)

kind regards,
Erik
 
Upvote 0
Erik,
Thanks! I'm pretty new to VBA so please pardon my ignorance.

The formula works fine when I type the value in a cell but when I copy the value to a cell from one sheet to another sheet only the value copies over not the cell shading. For example, sheet 2 would have (=sheet1!d4).

Again, thanks for the help.
 
Upvote 0
you're WELCOME
sorry missed your post yesterday

what you ask is more difficult, but it can be done

let's see if we agree on what you want to do

because you are talking about
I copy the value to a cell from one sheet to another sheet
but your example shows a formula
=sheet1!d4
that is confusing

is this what you want?
1. enter a formula (or paste link) in a cell (or some cells at the same time)
2. each cell you fill in this way, should get the background of the "precedent" (which means cell where formula is refering to)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top