Changes in an Excel File using VBscript ?

[leeland91]

Hello
this is my first post here, i am a new intern who was handed a task to find out of you can make some modifications to an EXCEL file based on some conditions

it is a job posting list, of a variety of data. the key data is

Date awarded date approved date moved

basically i am to change the cell color in date moved column if the date in DATE MOVED is greater than 7 days to RED

i have tried to work with VB script editor but i haven't the foggiest idea how to implement it...i have worked alot with VB in the past so i do know some things...

here is what i have so far


Dim DateAwarded As Date
Dim DateApproved As Date
Dim DateMoved As Date


DateAwarded = 6: k7 (i am not sure if this is even right)
DateApproved = 6: l7
DateMoved = 6: M7

If (DateMoved = DateApproved) Then
'change color of cell to green
Else

If (DateMoved > DateApproved) And (DateMoved < DateApproved + 7) Then
'Change color to yellow
Else

If (DateMoved > (DateApproved + 7)) Then
'Change Color to Red

End If



there are a bunch of other things they want me to check on like E-MAILING and so forth but that is a little down the road.



anyone who looks at this i appreciate it

leeland

 

[Sunny4Ever]

I don't have much time today but here is a few lines of vbscript to get you started at least:
--------------------------------------------
set oExcel = getobject(&quot;c:\exceltest.xls&quot
set oFirstSheet = OExcel.worksheets(&quot;sheet1&quot

if oFirstSheet.cells(1,1) <> &quot;&quot; then
msgbox oFirstSheet.cells(1,1)
end if
---------------------------------------------
This will look for a sheet on the root of C: called ExcelTest.xls and then display the contents of the first cell on Sheet1 if it is not blank.

Post back and I will try and assist some more tommorrow.

Thanks,
Sunny

 

[cparralesl]

Hello leeland91,

There are some other ways you can do this.

Fisrt, I don ot know if you have tried In Excel Menu: Format/Conditional Formatting. This could be on way.

The other Way you can hadle this using VBS, is as follow:


If WorkSheet.Cells(6, 3).Value = WorkSheet.Cells(6, 2).Value Then
Range(Cells(6, 3), Cells(6, 3)).Select
With Selection.Interior
.ColorIndex = 50
.Pattern = xlSolid
End With
ElseIf (DateMoved > DateApproved) And (DateMoved < DateApproved + 7) Then
Range(Cells(6, 3), Cells(6, 3)).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ElseIf (DateMoved > DateApproved) Then
Range(Cells(6, 3), Cells(6, 3)).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With


I hope this Pice of code can help you.
Cesar Humberto Parrales
Application Support
Home : 00505-289-2484
Mobil: 00505-8856-016