Conditional format with VBA

[Barrakooda]

Hi all

I am new to VBA so please bear with me. Having a few issues.

I would like to be able to click in a cell, select only the data in the column, sort from oldest to newest. Then click on a macro to conditional format the column (data only) of dates greater than 30, 60 & 90 days old.

I want to be able to use this on any sheet I am working in.

Is there anyway to combine the two lots of code below?

Thanks in advance

Sub SelectColumnData()
Dim ac As Range
Dim cr As Range
Dim col As Integer

Set ac = ActiveCell
Set cr = ac.CurrentRegion
col = ac.Column - cr.Column
If cr.Rows.Count > 1 Then
cr.Offset(1, col).Resize(cr.Rows.Count - 1, 1).Select
End If
End Sub

& (this is one I recorded using macro above but limit's itself to a particular sheet & cell)

Sub Macro1()

Range("G2").Select
Application.Run "PERSONAL.XLSB!SelectColumnData"
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
Range("G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A2:K429")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=TODAY()-30"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=TODAY()-60"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=TODAY()-90"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[Barrakooda]

My Apologies

This

"I would like to be able to click in a cell, select only the data in the column, sort from oldest to newest. Then click on a macro to conditional format the column (data only) of dates greater than 30, 60 & 90 days old."

Should read

"I would like to be able to click in a cell, select only the data in the column, sort from oldest to newest then conditional format the column (data in column previously selected) of dates greater than 30, 60 & 90 days old.

 

[Barrakooda]

Righto

Wit the below I can get it to work in any column on any sheet.

I am stuck on where it still references "G2", anyone know what I should put in its place to act on only data selected?

Sub GreaterThanXDays()
ActiveCell.Select
Dim ac As Range
Dim cr As Range
Dim col As Integer

Set ac = ActiveCell
Set cr = ac.CurrentRegion
col = ac.Column - cr.Column

If cr.Rows.Count > 1 Then
cr.Offset(1, col).Resize(cr.Rows.Count - 1, 1).Select

ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
Range("G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A2:K429")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=TODAY()-30"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=TODAY()-60"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=TODAY()-90"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
End Sub