VBA If Then Statement

[jtbrolly]

Hey guys,

Not that good with coding, trying to make an if-then statement basically saying if it says something in column c, then write this with this formatting in column a. I've been looking all over for the exact coding and can't seem to find how to do it. Would really appreciate any help.

Thanks,
Jimmy

 

[Sharpefiction]

Using the same code, but updated with what I think you need extra..

Sub Test()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim LR As Long, c As Range<o></o>
LR = Range("c" & Rows.Count).End(xlUp).Row<o></o>
For Each c In Range("c1:c" & LR)<o></o>
If c.Value <> "" Then ‘ checking for blanks<o></o>
If c.Value = “099” then ‘ checking for your specified value<o></o>
c.Offset(0, -2).Value = "Default" ‘ I’ve made the assumption here you only wanted the word ‘default’?
<o> c.Offset(0,-2).interior.colorindex = 3</o>
Else: end if<o></o>
Else: End If<o></o>
Next c<o></o>
End Sub<o></o>

So the addition I've made just asks the procedure to check if the cell has the value your want highlighted and if it has, write the word default in column A.

 

[jtbrolly]

Sub Test()
Dim LR As Long, c As Range
LR = Range("c" & Rows.Count).End(xlUp).Row
For Each c In Range("c1:c" & LR)
    If c.Value <> "" Then
        c.Offset(, -2).Value = "default"
    End If
Next c
    Columns("A:A").Select
    With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""default"""
    .FormatConditions(1).Interior.ColorIndex = 3
    End With
End Sub

This one is writing default and coloring for every cell, regardless of the contents of C.

 

[jtbrolly]

Using the same code, but updated with what I think you need extra..

Sub Test()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim LR As Long, c As Range<o></o>
LR = Range("c" & Rows.Count).End(xlUp).Row<o></o>
For Each c In Range("c1:c" & LR)<o></o>
If c.Value <> "" Then ‘ checking for blanks<o></o>
If c.Value = “099” then ‘ checking for your specified value<o></o>
c.Offset(0, -2).Value = "Default" ‘ I’ve made the assumption here you only wanted the word ‘default’?
<o>c.Offset(0,-2).interior.colorindex = 3</o>
Else: end if<o></o>
Else: End If<o></o>
Next c<o></o>
End Sub<o></o>

So the addition I've made just asks the procedure to check if the cell has the value your want highlighted and if it has, write the word default in column A.

This is it! Woohoo! Thank you so much!

Just two quick questions, how do I change the highlighted color to Yellow, I changed the color index to 6 but it's still coming up red.

And how do I add another number to look for. I'm trying this but it's not working:
If c.Value = “099” or 345 or "5JF" then

 

[hippiehacker]

This one is writing default and coloring for every cell, regardless of the contents of C.

sorry this is the code slightly changed the if then entry to o99

Sub Test()
Dim LR As Long, c As Range
LR = Range("c" & Rows.Count).End(xlUp).Row
For Each c In Range("c1:c" & LR)
    [B]If c.Value = "o99" Then
[/B]        c.Offset(, -2).Value = "default"
    End If
Next c
    Columns("A:A").Select
    With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""default"""
    .FormatConditions(1).Interior.ColorIndex = 3
    End With
End Sub

 

[hippiehacker]

now with all requirements

Sub Test()
Dim LR As Long, c As Range
LR = Range("c" & Rows.Count).End(xlUp).Row
For Each c In Range("c1:c" & LR)
    If c.Value = "o99" Or c.Value = "345" Or c.Value = "5JF" Then
        c.Offset(, -2).Value = "default"
    End If
Next c
    Columns("A:A").Select
    With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""default"""
    .FormatConditions(1).Interior.ColorIndex = 6
    End With
End Sub

 

[jtbrolly]

now with all requirements

Sub Test()
Dim LR As Long, c As Range
LR = Range("c" & Rows.Count).End(xlUp).Row
For Each c In Range("c1:c" & LR)
    If c.Value = "o99" Or c.Value = "345" Or c.Value = "5JF" Then
        c.Offset(, -2).Value = "default"
    End If
Next c
    Columns("A:A").Select
    With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""default"""
    .FormatConditions(1).Interior.ColorIndex = 6
    End With
End Sub

Works perfect! Great team work! Lol but in all seriousness thanks so much, you've both made my life 1000x easier.

Cheers!

 

[Sharpefiction]

This is it! Woohoo! Thank you so much!

Just two quick questions, how do I change the highlighted color to Yellow, I changed the color index to 6 but it's still coming up red.

And how do I add another number to look for. I'm trying this but it's not working:
If c.Value = “099” or 345 or "5JF" then

How about this update? Select Case is a wonderful alternative to 'if-then' statements
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Sub Test()
Dim LR As Long, c As Range
LR = Range("c" & Rows.Count).End(xlUp).Row
For Each c In Range("c1:c" & LR)
Select case c.Value<o></o>
Case “099”,”345”,”5JF” ‘ you can add as many as you like, just copy this format<o></o>
c.Offset(0, -2).Value = "Default"<o></o>
c.Offset(0,-2).interior.colorindex = 6<o></o>
Case else ‘ by leaving it blank under ‘case else’ it’s the same as saying ‘do nothing’<o></o>
End select<o></o>
Next c
End Sub

Sorry about the colorindex number being wrong, I didnt check it this time but I have this page (http://dmcritchie.mvps.org/excel/colors.htm) saved for referencing colours normally

 

[jtbrolly]

How about this update? Select Case is a wonderful alternative to 'if-then' statements
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Sub Test()
Dim LR As Long, c As Range
LR = Range("c" & Rows.Count).End(xlUp).Row
For Each c In Range("c1:c" & LR)
Select case c.Value<o></o>
Case “099”,”345”,”5JF” ‘ you can add as many as you like, just copy this format<o></o>
c.Offset(0, -2).Value = "Default"<o></o>
c.Offset(0,-2).interior.colorindex = 6<o></o>
Case else ‘ by leaving it blank under ‘case else’ it’s the same as saying ‘do nothing’<o></o>
End select<o></o>
Next c
End Sub

Sorry about the colorindex number being wrong, I didnt check it this time but I have this page (http://dmcritchie.mvps.org/excel/colors.htm) saved for referencing colours normally

That's a good idea, I might try Case instead because I have to do a bunch more of these and these seems like complicated. So if I wanted to add another Case with a different value to put in A, where would I put that, after Case else or before?

Example: Case 21J put None in A and highlight yellow.

 

[Sharpefiction]

That's a good idea, I might try Case instead because I have to do a bunch more of these and these seems like complicated. So if I wanted to add another Case with a different value to put in A, where would I put that, after Case else or before?

Example: Case 21J put None in A and highlight yellow.

Always before the case else, as thats your last stop-shop..

Case "your new value"
Code to execute here
Case else..

 

[jtbrolly]

Always before the case else, as thats your last stop-shop..

Case "your new value"
Code to execute here
Case else..

Perfect! Much better and cleaner than the If Then, great idea.

Thanks so much, I really appreaciate it.