| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format : return to default format
hello,
the following code allows me to add more than 3 conditional formats to fields in the range containing numbers -1, -0.5, 0.5 or 1. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else Exit Sub End Select Next cl End If End Sub i have two problems: - when i delete the values, i would like excel to turn to default formatting again automatically (i.e. no background and black color text). - how can i apply the code to values that are already in the range (have excel update the range automatically when i open the workbook) ? as you notice, i'm new to VBA... thanks. andy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional format : return to default format
Try this:-
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else cl.Interior.ColorIndex = xlNone cl.Font.ColorIndex = 0 Exit Sub End Select Next cl End If End Sub Mike "andy" wrote: hello, the following code allows me to add more than 3 conditional formats to fields in the range containing numbers -1, -0.5, 0.5 or 1. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else Exit Sub End Select Next cl End If End Sub i have two problems: - when i delete the values, i would like excel to turn to default formatting again automatically (i.e. no background and black color text). - how can i apply the code to values that are already in the range (have excel update the range automatically when i open the workbook) ? as you notice, i'm new to VBA... thanks. andy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional format : return to default format
hello Mike,
thanks, works just fine ! when i delete three values at a time, though, only the format of the first cell selected returns to default. can default format can be applied to all the selected fields? thanks again. andy "Mike H" wrote: Try this:- Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else cl.Interior.ColorIndex = xlNone cl.Font.ColorIndex = 0 Exit Sub End Select Next cl End If End Sub Mike "andy" wrote: hello, the following code allows me to add more than 3 conditional formats to fields in the range containing numbers -1, -0.5, 0.5 or 1. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else Exit Sub End Select Next cl End If End Sub i have two problems: - when i delete the values, i would like excel to turn to default formatting again automatically (i.e. no background and black color text). - how can i apply the code to values that are already in the range (have excel update the range automatically when i open the workbook) ? as you notice, i'm new to VBA... thanks. andy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional format : return to default format
Andy,
That should do it- Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else Selection.Interior.ColorIndex = xlNone cl.Font.ColorIndex = 0 Exit Sub End Select Next cl End If End Sub Mike "andy" wrote: hello Mike, thanks, works just fine ! when i delete three values at a time, though, only the format of the first cell selected returns to default. can default format can be applied to all the selected fields? thanks again. andy "Mike H" wrote: Try this:- Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else cl.Interior.ColorIndex = xlNone cl.Font.ColorIndex = 0 Exit Sub End Select Next cl End If End Sub Mike "andy" wrote: hello, the following code allows me to add more than 3 conditional formats to fields in the range containing numbers -1, -0.5, 0.5 or 1. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else Exit Sub End Select Next cl End If End Sub i have two problems: - when i delete the values, i would like excel to turn to default formatting again automatically (i.e. no background and black color text). - how can i apply the code to values that are already in the range (have excel update the range automatically when i open the workbook) ? as you notice, i'm new to VBA... thanks. andy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional format : return to default format
Andy,
Missed a line. Change both these lines:- Case Else cl.Interior.ColorIndex = xlNone cl.Font.ColorIndex = 0 to Case Else selection.Interior.ColorIndex = xlNone selection.Font.ColorIndex = 0 Mike "andy" wrote: hello Mike, thanks, works just fine ! when i delete three values at a time, though, only the format of the first cell selected returns to default. can default format can be applied to all the selected fields? thanks again. andy "Mike H" wrote: Try this:- Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else cl.Interior.ColorIndex = xlNone cl.Font.ColorIndex = 0 Exit Sub End Select Next cl End If End Sub Mike "andy" wrote: hello, the following code allows me to add more than 3 conditional formats to fields in the range containing numbers -1, -0.5, 0.5 or 1. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else Exit Sub End Select Next cl End If End Sub i have two problems: - when i delete the values, i would like excel to turn to default formatting again automatically (i.e. no background and black color text). - how can i apply the code to values that are already in the range (have excel update the range automatically when i open the workbook) ? as you notice, i'm new to VBA... thanks. andy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional format : return to default format
perfect ! thanks a lot.
"Mike H" wrote: Andy, Missed a line. Change both these lines:- Case Else cl.Interior.ColorIndex = xlNone cl.Font.ColorIndex = 0 to Case Else selection.Interior.ColorIndex = xlNone selection.Font.ColorIndex = 0 Mike "andy" wrote: hello Mike, thanks, works just fine ! when i delete three values at a time, though, only the format of the first cell selected returns to default. can default format can be applied to all the selected fields? thanks again. andy "Mike H" wrote: Try this:- Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else cl.Interior.ColorIndex = xlNone cl.Font.ColorIndex = 0 Exit Sub End Select Next cl End If End Sub Mike "andy" wrote: hello, the following code allows me to add more than 3 conditional formats to fields in the range containing numbers -1, -0.5, 0.5 or 1. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case 1 cl.Interior.ColorIndex = 11 cl.Font.ColorIndex = 11 Case 0.5 cl.Interior.ColorIndex = 41 cl.Font.ColorIndex = 41 Case -1 cl.Interior.ColorIndex = 16 cl.Font.ColorIndex = 16 Case -0.5 cl.Interior.ColorIndex = 15 cl.Font.ColorIndex = 15 Case Else Exit Sub End Select Next cl End If End Sub i have two problems: - when i delete the values, i would like excel to turn to default formatting again automatically (i.e. no background and black color text). - how can i apply the code to values that are already in the range (have excel update the range automatically when i open the workbook) ? as you notice, i'm new to VBA... thanks. andy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
| Conditional Format & Default Format Help | Excel Discussion (Misc queries) | |||
| How to return the default setting of numbering format in Excel? | Excel Discussion (Misc queries) | |||
| make a conditional format the default | Excel Discussion (Misc queries) | |||
| Keep format after paste from other worksheets - conditional format or EnableControl solution doesn't work | Excel Programming | |||
Linear Mode
