worksheet-change event in combination with a select case statement

[Abdul]

I've heard that combining "Worksheet-change event", along with "Select Case
Statement" can be used to change format of cells the same way as "Conditional
Formatting" is used, but I don't know how does one use those 2. I appreciate
examples.

 

[OssieMac]

Hi Abdul,

There are so many combinations of what can be done it will be much easier if
you can explain what cells you want to change formatting (ie. any cell on the
worksheet or cells in a specific range or specific column or row). aAso what
are the conditions for the change. ie. = to a value or >= to a value etc.

Also what version of Excel are you using?

 

[Hong Quach]

Hi Abdul,

Do you know how to use each separately? I mean, do you know how to setup a
worksheet-change event that trigger by change in a particular cell?

Private Sub Worksheet_change(ByVal Target As Range)
Dim ws As WorkSheet
Set ws = ActiveSheet
If (Target.Row = 5 And Target.Column = 5) Then
' Code to do thing here if the Cell "E5" changes
' This is where you would put the Select Case statement to decide what to
format or whatever you want Excel to do.
End If
End Sub

Part 2 of the question is the Select Case statement. You don't have to use
Select-case statement if you not familiar with it, just use nested If Else
statements.

Hong Quach

 

[The Code Cage Team]

Sure, its done like this:


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim CVal As String
If Target.Cells.Count > 1 Then Exit Sub
CVal = Target
Set MyRange = Range("A120")

If Not Intersect(Target, MyRange) Is Nothing Then
Select Case CVal
Case "Monday"
Target.Interior.ColorIndex = 5
Case "Tuesday"
Target.Interior.ColorIndex = 10
Case "Wednesday"
Case vbNullString
Target.Interior.ColorIndex = xlNone
End Select
End If
End Su
-------------------

--
The Code Cage Tea

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com

 

[Abdul]

thanks very much for the example, but how can u make excel change the cell's
fill color and pattern type?

 

[Abdul]

Cells range is E2:F19
and the type of change I want here is if cell equals to "item1" then format
cell patteren type 1 cell fill color red text color blue
if cell equals to "item2" then format cell pattern type 2 cell fill color
black, text color white. etc

using excel 2003

 

[Abdul]

no Hong Quach, I'm not aware of how to use separately, I did try however the
example showen on the help on the VB console that comes with excel "view
code", but I'm not sure how to make it change colors for specified sheet only
with selected range.
I'm also using links to this range in multiple sheets, that is, its visible
in multiple sheets but can only be changed from one sheet. is it possible to
make the macro change the format for the links aswell?

 

[The Code Cage Team]

Abdul, the code i posted should be enough for you to experiment with,
the code already fills the interior, why not use the macro recorder when
you manually change a cells pattern then view the code and adapt it to
the code i gave you, it really is the only way to learn and understand
what is taking place.


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)