PDA

View Full Version : Solved: Color columns basis input values



JohnyG
10-30-2008, 11:46 PM
Hi,

I have two sheets Sheet1 and Sheet2. The requirement is such that I need to enter values in Sheet1 and basis the values entered in sheet1 the cells in sheet2 get colored.

for instance

I have entered "2" in Sheet1!A2 as a result 2 columns starting from Sheet2!A2 i.e. (Sheet2 cell B2 and C2) get highlighted(colored).

Similarly if I entered "3" in Sheet1!A3 as a result 3 columns starting from Sheet3!B3 i.e. (Sheet2 cell B3, C3 and D3) get highlighted(colored).

Regards,

Johny

Bob Phillips
10-31-2008, 03:29 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If IsNumeric(.Value) Then

Worksheets("Sheet2").Cells(.Row, "B").Resize(, .Value).Interior.ColorIndex = 38
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

JohnyG
10-31-2008, 05:39 AM
Thanks xld,

It works for me ..


Regards,
Johny