Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet-change event in combination with a select case statement
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet-change event in combination with a select case statement
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? -- Regards, OssieMac "Abdul" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet-change event in combination with a select case statement
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 "Abdul" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet-change event in combination with a select case statement
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("A1:D20") 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 Sub -------------------- -- The Code Cage Team Regards, The Code Cage Team 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=37790 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet-change event in combination with a select case state
thanks very much for the example, but how can u make excel change the cell's
fill color and pattern type? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet-change event in combination with a select case state
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet-change event in combination with a select case state
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet-change event in combination with a select case statement
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) ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=37790 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Case Statement | Excel Worksheet Functions | |||
Using Like Statement in Select Case | Excel Programming | |||
For...Next or Select Case Statement | Excel Programming | |||
select case statement? | Excel Programming | |||
select case statement | Excel Programming |