New to programming VB in Excel

[Guest]

Hello,

I'm new to programming in Excel and I'm trying to learn how to write VB in
Excel and then run the macro. I'm using Excel 2003 and I'm trying to start
with something simple. Here is what I'm trying but I think there is a syntex
error:

I'm trying to change the color background for cell G4 based on the value of
cell F4. If G4 is less than F4, I want the background of G4 to change to Red
indicating this cell is not completed. If it is = to F4, I want G4
background to change to Green indicating it is completed.

Here is my code:

If (F4 < G4) Then G4.Background = vbRed
Else: G4.Background = vbGreen
End If

Thanks in advance from your help.

 

[Nick]

No programming required.
Take a look at Conditional Formatting in the Help file.

Nick

 

[Tom Ogilvy]

If Range("F4").Value < Range("G4").Value Then
Range("G4").Interior.Color = vbRed
Else
Range("G4").Interior.Color = vbGreen
End If

 

[Bernie Deitrick]

Rick,

Conditional formatting is the way to go, but as far as your code, here is
the way that you would actually code what you wanted:

If Range("F4").Value < Range("G4").Value Then
Range("G4").Interior.Color = vbRed
Else
Range("G4").Interior.Color = vbGreen
End If

Of course, there is a disconnect between your psuedo-code snippet and your
word descrioption. Perhaps you really want

If Range("G4").Value < Range("F4").Value Then
Range("G4").Interior.Color = vbRed
ElseIf Range("G4").Value = Range("F4").Value Then
Range("G4").Interior.Color = vbGreen
Else
Range("G4").Interior.ColorIndex = xlNone
End If


HTH,
Bernie
MS Excel MVP

 

[Guest]

however, if it's just learning programing for the sake of it then read on.
First off you don't mention what the error is you are reciveing. I think
that it might be due to the use of G$ and F4, if not then VB for Excel 2K3 is
much better than 2K, but I digress.

G4 and F4 are variable names as far as VB is concerned not cells. You need
to use the cells or range properties:

worksheets("<Sheet name>").cells(<row>,<col>).value

or

Range property to select multiple cells.

So, your code becomes:

firstval = worksheets("Sheet1").cells(row1, col1).value
secval = worksheets("Sheet1").cells(row2, col2).value

If (firstval < secval) Then
worksheets("Sheet1").cells(row1, col1).color.Background = vbRed
Else
worksheets("Sheet1").cells(row2, col2).color.Background = vbGreen
End If

firstval and secval are just to seperate the assignment from the comparison.

"Sheet1" is the default name for the first sheet you can use an index (0,1,2
etc or a the name "Sheet2" etc)

if you miss 'value' from the "worksheets("Sheet1").cells(row1, col1).value"
then you get the formula or link or whatever is in the cell. If you use
value then Excel transforms the data to the expected output.

row1,col1,row2,col2 can be literals if you like or constants if it's always
the same cells you are checking. (literals are BAD)

as for the colour property I really can't remember if the cells property
exposes the colour/font etc. I seem to remember that it does but it is very
messy.

I hope this helps, I'm not very good at this helping thing so feel free to
post and rant at my poor style/help etc.

 

[Charyn]

Well I sure hope no-one rants about your helping!

I am also just now learning VBA beyond the "Click Record Macro" stage and I
joined this NG to get a feel for the language.

Needless to say, it's overwhelming to us newbies in here!!

To thosee that take the time to patiently redirect us to sites that can
help, or take the time to patiently explaing for the zillionth time a
concept: THANKS!! from the lurking newbies

~Charyn