Silkroad Online Forums

Posted: **Wed Mar 24, 2010 12:52 pm**

I have this database but I want the dates that have expired over 1 year to be automatically highlighted in red. I know it can be done because I remember someone showing me a year or so ago, anyone have any idea how to do it, or what function to use? Here's come visual aid ^^

Ta very much!

Posted: **Wed Mar 24, 2010 3:04 pm**

You have to learn VBA to do it:
- read the content of a cell
- analyse the date from the string
- compare
- highlight the cell

Posted: **Wed Mar 24, 2010 3:29 pm**

NuclearSilo wrote:You have to learn VBA to do it:
- read the content of a CeLL
- analyse the date from the string
- compare
- highlight the CeLL

you just cant stop talkin about me can you silo?

Posted: **Wed Mar 24, 2010 4:02 pm**

I cant find a way to do this without using VB. If u know VB codings, try these.

Have 1 cell on each "Last Paid" column to calculate the date differences between the date on the column and the current date. U can hide that column later.

The function on cell T8
=DAYS360(S8,TODAY())

The function on cell T9
=DAYS360(S9,TODAY())

The function on cell T10
=DAYS360(S10,TODAY())

and so on. Make sure the format for the new column is set to "number".
After that, create a command button under ur table to act as a refresh button.

The coding for the refresh button should be like this :-

Code: Select all

Private Sub CommandButton1_Click()
    
    If Range("T8").Value > 365 Then
        With Range("S8").Font
            .Color = -16776961
            .Bold = True
        End With
    Else
        With Range("S8").Font
            .ColorIndex = xlAutomatic
            .Bold = False
        End With
    End If
    
    If Range("T9").Value > 365 Then
        With Range("S9").Font
            .Color = -16776961
            .Bold = True
        End With
    Else
        With Range("S9").Font
            .ColorIndex = xlAutomatic
            .Bold = False
        End With
    End If
    
    If Range("T10").Value > 365 Then
        With Range("S10").Font
            .Color = -16776961
            .Bold = True
        End With
    Else
        With Range("S10").Font
            .ColorIndex = xlAutomatic
            .Bold = False
        End With
    End If

End Sub

I believe the coding can be more simple, but I'm too lazy to think of other ways. Maybe these steps can give u the idea on how to do it. Hope it helps.

Posted: **Wed Mar 24, 2010 5:40 pm**

CeLL wrote:
NuclearSilo wrote:You have to learn VBA to do it:
- read the content of a CeLL
- analyse the date from the string
- compare
- highlight the CeLL

you just cant stop talkin about me can you silo?

I can't. You are too famous

@OP: Here is the macro you need to create
- Open Visual Basic Editor (Alt+F11)
- Insert a module
- Insert the following code into the module then save

Code: Select all

Sub HighlightExpired()
    Rng = Selection.Rows.Count
    Dim value As Date
    Dim str As String
    
    Selection.NumberFormat = "dd/mm/yyyy"
    
    For i = 1 To Rng
        str = ActiveCell.Cells(i)
        If str <> "" Then
            value = str
            If DateDiff("d", value, Date) > 365 Then    'if > 365 day
                '1=black, 2=white, 3=red, 4=green, 5=blue, 6=yellow
                ActiveCell.Cells(i).Interior.ColorIndex = 3
            End If
        End If
    Next i
End Sub

How to run?
- Select a range of cell you want to check
- Run the macro (Alt+F8)
- Choose HighlightExpired then run it

PS: this is made under Excel 2003, the hotkey like alt+F11,Alt+F8 could do different thing as mentioned on different version of Excel

Posted: **Wed Mar 24, 2010 9:58 pm**

Thanks to Silo for spending over an hour helping me :love:

Posted: **Wed Mar 24, 2010 10:29 pm**

Np.

Try to find the ID/name of that combo box. We'll see it together tomorrow if you still didn't find it

Posted: **Thu Mar 25, 2010 8:11 am**

Your school is weird

Giving you assignments you cant do, should tell them to teach you the stuff before they give you homework about it ^^

Silkroad Online Forums

Anyone good with Excel?

Anyone good with Excel?

Re: Anyone good with Excel?

Re: Anyone good with Excel?

Re: Anyone good with Excel?

Re: Anyone good with Excel?

Re: Anyone good with Excel?

Re: Anyone good with Excel?

Re: Anyone good with Excel?