| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting via VBA
Hi gang
I am trying to use this code to achieve grreater than 3 condition CF. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:a100")) Is Nothing Then With Target Select Case LCase(.Value) Case "Case1": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 3 Case "Case2": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 5 Case "Case3": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 10 Case "Case4": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 19 Case "Case5": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 20 Case "Case6": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 34 End Select End With End If ws_exit: Application.EnableEvents = True End Sub 2 Questions... 1 How can I add and AND to the case? IE., Case1 is "Dog" and 0. (I want to color only cells that are greater than 0.) 2 The formatting is on a report that is not updated except by links to another Sheet. Neither change event or calculate event as I see it will really work in this instance without having to go back and over type the values in A1:A100. Can I adapt the code so I can assign it to a button and run it from there, without having to go overtype all of the values A1:A100? Thanks! Steve |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting via VBA
Not sure how a cell can both be equal to "Dog" and 0, so I assume you want
to check some other cell in proximity to column A on the row being checked. Also, as written the code looks for the specific strings "Case1", "Case2", etc. I assume your recognize this. Private Sub Commandbutton1_Click() On Error GoTo ws_exit: Application.EnableEvents = False For each cell in Me.Range("A1:A100") With cell if cell.Offset(0,1).Value 0 then Select Case LCase(.Value) Case "Case1": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 3 Case "Case2": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 5 Case "Case3": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 10 Case "Case4": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 19 Case "Case5": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 20 Case "Case6": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 34 End Select End if End With Next ws_exit: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Steve" wrote in message ... Hi gang I am trying to use this code to achieve grreater than 3 condition CF. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:a100")) Is Nothing Then With Target Select Case LCase(.Value) Case "Case1": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 3 Case "Case2": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 5 Case "Case3": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 10 Case "Case4": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 19 Case "Case5": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 20 Case "Case6": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 34 End Select End With End If ws_exit: Application.EnableEvents = True End Sub 2 Questions... 1 How can I add and AND to the case? IE., Case1 is "Dog" and 0. (I want to color only cells that are greater than 0.) 2 The formatting is on a report that is not updated except by links to another Sheet. Neither change event or calculate event as I see it will really work in this instance without having to go back and over type the values in A1:A100. Can I adapt the code so I can assign it to a button and run it from there, without having to go overtype all of the values A1:A100? Thanks! Steve |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting via VBA
Thanks for the reply Tom
looks like this may work. you did well in translating my greater than 0 part. The cells to the right of Col 1 0 are the ones I wanted formatted and color based on the Case in ColA. I have created the button from the controls menu and when I R-Click the button and View code, the code shows up, but I cannot click the button. What am I doing wrong? Thanks Steve "Tom Ogilvy" wrote: Not sure how a cell can both be equal to "Dog" and 0, so I assume you want to check some other cell in proximity to column A on the row being checked. Also, as written the code looks for the specific strings "Case1", "Case2", etc. I assume your recognize this. Private Sub Commandbutton1_Click() On Error GoTo ws_exit: Application.EnableEvents = False For each cell in Me.Range("A1:A100") With cell if cell.Offset(0,1).Value 0 then Select Case LCase(.Value) Case "Case1": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 3 Case "Case2": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 5 Case "Case3": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 10 Case "Case4": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 19 Case "Case5": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 20 Case "Case6": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 34 End Select End if End With Next ws_exit: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Steve" wrote in message ... Hi gang I am trying to use this code to achieve grreater than 3 condition CF. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:a100")) Is Nothing Then With Target Select Case LCase(.Value) Case "Case1": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 3 Case "Case2": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 5 Case "Case3": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 10 Case "Case4": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 19 Case "Case5": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 20 Case "Case6": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 34 End Select End With End If ws_exit: Application.EnableEvents = True End Sub 2 Questions... 1 How can I add and AND to the case? IE., Case1 is "Dog" and 0. (I want to color only cells that are greater than 0.) 2 The formatting is on a report that is not updated except by links to another Sheet. Neither change event or calculate event as I see it will really work in this instance without having to go back and over type the values in A1:A100. Can I adapt the code so I can assign it to a button and run it from there, without having to go overtype all of the values A1:A100? Thanks! Steve |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting via VBA
You need to take it out of design mode (the upper left button on the
control toolbox with the drawing triangle symbol should appear depressed. Click it so it does not appear depressed and you are out of design mode). -- Regards, Tom Ogilvy "Steve" wrote in message ... Thanks for the reply Tom looks like this may work. you did well in translating my greater than 0 part. The cells to the right of Col 1 0 are the ones I wanted formatted and color based on the Case in ColA. I have created the button from the controls menu and when I R-Click the button and View code, the code shows up, but I cannot click the button. What am I doing wrong? Thanks Steve "Tom Ogilvy" wrote: Not sure how a cell can both be equal to "Dog" and 0, so I assume you want to check some other cell in proximity to column A on the row being checked. Also, as written the code looks for the specific strings "Case1", "Case2", etc. I assume your recognize this. Private Sub Commandbutton1_Click() On Error GoTo ws_exit: Application.EnableEvents = False For each cell in Me.Range("A1:A100") With cell if cell.Offset(0,1).Value 0 then Select Case LCase(.Value) Case "Case1": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 3 Case "Case2": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 5 Case "Case3": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 10 Case "Case4": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 19 Case "Case5": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 20 Case "Case6": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 34 End Select End if End With Next ws_exit: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Steve" wrote in message ... Hi gang I am trying to use this code to achieve grreater than 3 condition CF. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:a100")) Is Nothing Then With Target Select Case LCase(.Value) Case "Case1": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 3 Case "Case2": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 5 Case "Case3": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 10 Case "Case4": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 19 Case "Case5": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 20 Case "Case6": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 34 End Select End With End If ws_exit: Application.EnableEvents = True End Sub 2 Questions... 1 How can I add and AND to the case? IE., Case1 is "Dog" and 0. (I want to color only cells that are greater than 0.) 2 The formatting is on a report that is not updated except by links to another Sheet. Neither change event or calculate event as I see it will really work in this instance without having to go back and over type the values in A1:A100. Can I adapt the code so I can assign it to a button and run it from there, without having to go overtype all of the values A1:A100? Thanks! Steve |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting via VBA
Just another idea for your Select Case statement:
S = .value If UCase(S) Like "CASE[123456]" Then .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = _ 99109085 Mod (4 * Right(S, 1) + 13) End If HTH Dana DeLouis Select Case LCase(.Value) Case "Case1": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 3 Case "Case2": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 5 Case "Case3": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 10 Case "Case4": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 19 Case "Case5": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 20 Case "Case6": .Offset(0, 1).Resize(1, 7).Interior.ColorIndex = 34 End Select <snip |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
| Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
| Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
| Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
| expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions | |||
Hybrid Mode
