Help with some VBA code - I Only want the cell in a column to highlight in red not the whole row

[AxMan]

Hi All,

I'm having an issue with some vba coding, I basically want it to highlight the cells in column p where the (current date - 3 days) rule applies.

I am currently getting the whole row that highlights rather than the singular cell in column p.

Code is shown below:-

Sub HighlightDates()


Dim lngLastRow As Long, lngRow As Long
Dim Col As Long
Dim strColumn As String

strColumn = "P"
With ActiveSheet
lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
For lngRow = 2 To lngLastRow
If IsDate(.Cells(lngRow, strColumn).Value) And (.Cells(lngRow, strColumn).Value) >= Date - 3 Then
.Rows(lngRow).Interior.ColorIndex = 3
End If
Next lngRow
End With


End Sub

I would appreciate any help with this,

Thanks!

 

[Joe4]

Welcome to the Board!

This is the issue here:

[COLOR=#333333].Rows(lngRow).Interior.ColorIndex = 3[/COLOR]

You are changing the color of the whole row.
Try

.Cells(lngRow,"P")[COLOR=#333333].Interior.ColorIndex = 3[/COLOR]

But, maybe a better question may be why you are using VBA, and not Conditional Formatting?

 

[My Aswer Is This]

I think your script should look like the one below. Note changes highlighted in red.

Sub HighlightDates()


 Dim lngLastRow As Long, lngRow As Long
 Dim Col As Long
 Dim strColumn As String

 strColumn = "P"
 With ActiveSheet
 lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
 For lngRow = 2 To lngLastRow
 If IsDate(.Cells(lngRow, strColumn).Value) And (.Cells(lngRow, strColumn).Value) >= Date - 3 Then
 [COLOR="#FF0000"].Cells(lngRow, strColumn)[/COLOR].Interior.ColorIndex = 3
 End If
 Next lngRow
 End With


 End Sub

 

[AxMan]

Thank you both for your quick responses, greatly appreciated! I'm not using conditional formatting as eventually I want the highlighted cells to flash red and white rather than just be red.

I also wanted to ask if I'm doing the correct thing in the first place, I'm trying to get the date in cell p2, p3 etc to highlight red when p2 is greater than p2 - 3 days based on the current date, how can i do this?

 

[AxMan]

Let me explain it a bit better.... I want the cell to highlight red in cell p2 when: (P2-3) >= Current Date <= P2.

I would really appreciate the help!

P.S. - I'm quite new to VBA coding

 

[Joe4]

Have you tried the code provided?
Did it work?

If not, can you lay out an example for us (what values you have in what cells and what you expected result is)?

BTW, I have never tried to make blinking cells (never really felt it was worth the effort), but here is some VBA code that shows you how to do that:
https://msdn.microsoft.com/en-us/library/office/ff193220.aspx

 

[AxMan]

Hi Joe,

I have indeed tried the code provided, it worked a charm... thank you!

I have actually come up with a decent solution to the problem I had, I just repeated the code and made a few changes to it as seen below:-

Sub HighlightDates()

Dim lngLastRow As Long, lngRow As Long
Dim Col As Long
Dim strColumn As String

strColumn = "P"

With ActiveSheet
lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
For lngRow = 2 To lngLastRow
If IsDate(.Cells(lngRow, strColumn).Value) And (.Cells(lngRow, strColumn).Value) - 3 >= Date _
<= IsDate(.Cells(lngRow, strColumn).Value) And (.Cells(lngRow, strColumn).Value) Then
.Cells(lngRow, strColumn).Interior.ColorIndex = 3


Else
.Cells(lngRow, strColumn).Interior.ColorIndex = 2
End If
Next lngRow
End With


With ActiveSheet

lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
For lngRow = 2 To lngLastRow
If IsDate(.Cells(lngRow, strColumn).Value) And (.Cells(lngRow, strColumn).Value) - 3 <= Date Then
.Cells(lngRow, strColumn).Interior.ColorIndex = 3

Else
.Cells(lngRow, strColumn).Interior.ColorIndex = 2
End If
Next lngRow
End With



End Sub

I've also never tried blinking cells but the code I have for it works well with a "flashing" cell style that you incorporate in the column's cells.

Thanks for your help so far.