Highlight cells on same row as active cell

st3pfi · Nov 8th 2010

Hi I do need help here.

I have a spreedsheet where I need to highlight from row A to row F of an active cell. I research through all the forums and there is a need for "Private sub" but I already have a "private sub". Is it possible to have 2 sub within a code? My idea is to have a spreedsheet where it would firstly have a message box once it is open, secondly it would search for =Today()-7days and lastly, highlighting from row A to row F. I have done up till =TODAY()-7 however I could not find any solution for highlight from row A to row F.

As following is my code:

Code

Public Sub Workbook_Open()
MsgBox "Please remember to check the highlighted duedates.", vbInformation + vbOKOnly, "Gentle Reminder"
Columns("B:B").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$A$1-7"
Selection.FormatConditions(1) _
.Interior.ColorIndex = 20
End Sub

Can anyone please help me? Thank You.

drnaphtali · Nov 8th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

Hi steph,
Your question is very unclear for me. Do you want a macro which will loop through, say column A, to see to check the due dates, and if the date is late, highlite the row (columns A to F)? Does the code which you provided not work? I see you added the macro to add the conditional format column B.

Regards,
Leon

jimmyheng1987 · Nov 8th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

I think is should be Columns("B:2").Select

littleiit · Nov 8th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

Try Below one

Code

Public Sub Workbook_Open()
Dim cell As Range
MsgBox "Please remember to check the highlighted duedates.", vbInformation + vbOKOnly, "Gentle Reminder"
Range("AFL1").Copy
Columns("A:F").Select
Selection.PasteSpecial xlPasteFormats
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$A$1-7"
For Each cell In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
If cell.Value = CDate(Date) - 7 Then
cell.Offset(, -1).Resize(1, 6).Select
Selection.Interior.ColorIndex = 20
End If
Next
 Columns("A:B").NumberFormat = "mm/dd/yyyy"
 Application.CutCopyMode = False
End Sub

Display More

Rob Xaos · Nov 8th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

st3pfi,

Please note that you must use [noparse]

Code

[/noparse] tags when including VBA in your posts. Please see the Tags link in my signature for more detail. I have added the tags for you this time only.

st3pfi · Nov 9th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

hi drnaphtali,

I am sorry that my question is unclear.

I would like to have a

Firstly, code/marco which enable me to check through the dates which is in column B where today- 7 working days.

Secondly, if the dates are being selected, the active cell row would be selected (row A to row F).

The code I provided only work till today - 7 days and it does not highlight the active cell row. As I do not know the code for format conditioning therefore, i record a macro and copied the code over.

st3pfi · Nov 9th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

hi littleiit,

I tried your code however it prompt method 'range' of object '_gobal' failed. can you kindly help me with it? I sucks at vba. Thanks.

littleiit · Nov 9th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

You might be using excel 2003.

Try below one.

Code

Private Sub Workbook_Open()
MsgBox "Please remember to check the highlighted duedates.", vbInformation + vbOKOnly, "Gentle Reminder"
Columns("B:B").FormatConditions.Delete


    Columns("A:F").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=$B1=($A$1-7)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    
    With Selection.FormatConditions(1).Interior
    
    .ColorIndex = 20
    End With
    
Selection.FormatConditions(1).StopIfTrue = False
End Sub

Display More

st3pfi · Nov 9th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

sorry that i didn't indicate I am using excel 2003, i tried the code but it still prompt me "run time error '1004' application-defined or object-defined error. can you please kindly help me with this? Thank You.

littleiit · Nov 9th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

Code

Private Sub Workbook_Open()
    MsgBox "Please remember to check the highlighted duedates.", vbInformation + vbOKOnly, "Gentle Reminder"
    Columns("A:F").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$B1=($A$1-7)"
    Selection.FormatConditions(1).Interior.ColorIndex = 20
End Sub

I have test the above code on 2003. its working.. Please try

st3pfi · Nov 9th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

it is working. Can I ask another question, what's the code for deducting 7 working days instead of normal 7 days?

littleiit · Nov 9th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

Try this

Code

Private Sub Workbook_Open()
    I = 0
    While I < 7
    If Weekday(CDate(Range("A1")) - j, vbSaturday) = 1 Or Weekday(CDate(Range("A1")) - j, vbSunday) = 1 Then
    Else
    I = I + 1
    End If
    x = CDate(Date) - j
        If I <> 7 Then
            j = j + 1
        End If
    Wend
MsgBox x
    MsgBox "Please remember to check the highlighted duedates.", vbInformation + vbOKOnly, "Gentle Reminder"
    Columns("A:F").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$B1=($A$1-" & j & ")"
    Selection.FormatConditions(1).Interior.ColorIndex = 20
End Sub

Display More

I have excluded the Saturday and Sunday.

st3pfi · Nov 9th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

Thanks littleiit. But when I run the code, there would be a msgbox "the due date" before my msgbox "Gentle reminder" could be displayed. Can that msgbox be removed?

Another issue would be, I forget there's public holidays. I researched online, there is a need to key in the individual dates or alternative would be country' s holidays? Something like =WORKDAY(A10,-7).

littleiit · Nov 9th 2010

Re: Conditional formatting, Highlighting of row A to row F when of active cell

Code

Private Sub Workbook_Open()
    Dim blnFindDate As Boolean
    I = 0
    While I < 7
    If Weekday(CDate(Range("A1")) - j, vbSaturday) = 1 Or Weekday(CDate(Range("A1")) - j, vbSunday) = 1 Then
 
    Else
        If Range("[B]Z1[/B]").EntireColumn.Find(CDate(Range("A1")) - j) Is Nothing Then
            I = I + 1
        End If
    End If
    x = CDate(Date) - j
        If I <> 7 Then
            j = j + 1
        End If
 
    Wend
    MsgBox "Please remember to check the highlighted duedates.", vbInformation + vbOKOnly, "Gentle Reminder"
    Columns("A:F").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$B1=($A$1-" & j & ")"
    Selection.FormatConditions(1).Interior.ColorIndex = 20
End Sub

Display More

Put your Public Holidays in Column Z if you want to put any where else change the column written in code ("Z1" written in bold ) let say you are changing it to column AA then in Place of Z write AA

Highlight cells on same row as active cell

Participate now!

Share