VBA Conditional Formatting

[Jbigler]

I am currently searching everywhere to figure out code to do the following:


1. Highlight all cells within COLUMN A that are set to the current date

a)bold and fill cell with a color (lets say green)

b)select that current date ROW through column P

c)Add a thick upper border


2. Fill all cells with red that are above the current date row in range (B**)


3. Add a thick bottom border to all date values in column A that foll on a Friday ( I am actually trying to have weeks separated out by borders, so sometimes there may not be a friday in the date range and I still need to apply a border to separate different weeks.


I appreciate the help!

 

[SirJB7]

Hi, Jbigler!


1. a) : conditional formatting, condition =A1=TODAY()


1. b) & c), & 2. : only by macro, but what if more than one as asked "all cells"?

-----

[pre]

Dim rngI As Range, rngO As Range, rngW As Range, I As Integer
Set rngI = ActiveSheet.Range("A1:C100")
Set rngO = Nothing
For I = 1 To rngI.Rows.Count
If rngI.Cells(I, 1).Value = Int(Now()) Then
Set rngW = Range(rngI.Cells(I, 1), rngI.Cells(I, 16))
Set rngO = Application.Union(rngO, rngW)
If I > 1 Then
With Range(rngW.Cells(1, 2).Offset(-1, 0), rngW.Cells(1, 16).Offset(-1, 0))
With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With
End If
End If
Next I
With rngO
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
Set rngW = Nothing
Set rngO = Nothing
Set rngI = Nothing

[/pre]
-----


I leave 3. for you to give a try and play a little with the built-in macro recording features of Excel.


Regards!