Conditional Formatting
 

When doing conditional formatting and we wont to put it into VBA what does it
go under for the action to take place.

Do I create another Sub or Function and how does it activate.

Thanks
Noemi

Conditional Formatting
 

this is some code from one of my modules, maybe it will give you and idea.
it does a sort operation first on the selected range and then colors odd
rows blue


With rng
..Sort Key1:=Range("header3").Offset(0, 1), Order1:=xlDescending,
Key2:=Range( _
"header3"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=1"

With .FormatConditions(1).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.FormatConditions(1).Interior.ColorIndex = 37
End With

--


Gary


"Noemi" wrote in message
...
When doing conditional formatting and we wont to put it into VBA what does
it
go under for the action to take place.

Do I create another Sub or Function and how does it activate.

Thanks
Noemi

Conditional Formatting
 

Hi Gary
what do you put the code under.

as we are not using a button how do you activate the code.

I tried putting under the selection change but it would not let me do
anything else.

Thanks
Noemi

"Gary Keramidas" wrote:

this is some code from one of my modules, maybe it will give you and idea.
it does a sort operation first on the selected range and then colors odd
rows blue


With rng
..Sort Key1:=Range("header3").Offset(0, 1), Order1:=xlDescending,
Key2:=Range( _
"header3"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=1"

With .FormatConditions(1).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.FormatConditions(1).Interior.ColorIndex = 37
End With

--


Gary


"Noemi" wrote in message
...
When doing conditional formatting and we wont to put it into VBA what does
it
go under for the action to take place.

Do I create another Sub or Function and how does it activate.

Thanks
Noemi

Conditional Formatting
 

goes in with your code, in sheet code or a code module. just paste this in a
test workbook, either in a module or on the sheet's code page.

it will color odd rows between 1 and 20 in columns a through e blue

Option Explicit
Dim rng As Range

Sub color_rows()
Set rng = Range("a1:e20")
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=1"

With .FormatConditions(1).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.FormatConditions(1).Interior.ColorIndex = 37
End With



End Sub

--


Gary


"Noemi" wrote in message
...
Hi Gary
what do you put the code under.

as we are not using a button how do you activate the code.

I tried putting under the selection change but it would not let me do
anything else.

Thanks
Noemi

"Gary Keramidas" wrote:

this is some code from one of my modules, maybe it will give you and
idea.
it does a sort operation first on the selected range and then colors odd
rows blue


With rng
..Sort Key1:=Range("header3").Offset(0, 1), Order1:=xlDescending,
Key2:=Range( _
"header3"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=1"

With .FormatConditions(1).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.FormatConditions(1).Interior.ColorIndex = 37
End With

--


Gary


"Noemi" wrote in message
...
When doing conditional formatting and we wont to put it into VBA what
does
it
go under for the action to take place.

Do I create another Sub or Function and how does it activate.

Thanks
Noemi

Conditional Formatting
 

and if you like, run this macro and it will show you the background colors
for the corresponding number

Option Explicit
Dim rng As Range
Dim i As Integer
Sub color_rows()
Application.ScreenUpdating = False
i = 1
For i = 1 To 56
Set rng = Rows(i)
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ROW()"
.FormatConditions(1).Interior.ColorIndex = i
With .FormatConditions(1).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End With
Next
Application.ScreenUpdating = True
End Sub

--


Gary


"Noemi" wrote in message
...
Hi Gary
what do you put the code under.

as we are not using a button how do you activate the code.

I tried putting under the selection change but it would not let me do
anything else.

Thanks
Noemi

"Gary Keramidas" wrote:

this is some code from one of my modules, maybe it will give you and
idea.
it does a sort operation first on the selected range and then colors odd
rows blue


With rng
..Sort Key1:=Range("header3").Offset(0, 1), Order1:=xlDescending,
Key2:=Range( _
"header3"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=1"

With .FormatConditions(1).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.FormatConditions(1).Interior.ColorIndex = 37
End With

--


Gary


"Noemi" wrote in message
...
When doing conditional formatting and we wont to put it into VBA what
does
it
go under for the action to take place.

Do I create another Sub or Function and how does it activate.

Thanks
Noemi