VBA Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a VBA Code snippet which I am using to get around the conditional
format restriction.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

<== Start Code snippet ==>
' Define conditional formats
If Not Intersect(Target, Range("E4:IV1003")) Is Nothing Then
Select Case Target
Case ""
icolor = 0
' Define Code e.g. "Code" or "Description"
Case "A"
' Define color as stated in the color index.
icolor = "3"
Case "Test"
icolor = 4
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub
<== END Code snippet ==>

Currently I have this in Worksheet1. In worksheet2 I have a table where I
want to define "A" and "1".

Can you please let me know how I can reference Worksheet2!C2 where the value
for "A" in and Worksheet2!D2 where the value for "1" would be defined?

Thanks in advance for your support.

Vince
 
Are you trying to change ws2 from a worksheet change event in ws1? If so

case "A" then sheets("sheet2").range("a1").interior.colorindex=3

If not, pls explain
 
In the Range E4:IV1003 in ws1 I will have to insert different codes.
The color codes and the codes will be specified in ws2.

e.g. :
A1: Description | B1: Code | C1: Color Index
A2: Concept Test | B2: CT | C2: 3

Case needs to be read from WS2-B2
icolor needs to be read from WS2-C2

Thanks in advance
 
Back
Top