ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Conditional format : return to default format (https://www.excelbanter.com/excel-programming/392174-conditional-format-return-default-format.html)

Andy
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

Mike H
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

Andy
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

Mike H
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

Mike H
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

Andy
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


All times are GMT +1. The time now is 05:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com