Stuck trying to create VBA code to Apply formatting based on Cell Values

[CharBram]

Hello Everyone!</SPAN>

I am completely lost trying to write code to apply formatting based on certain conditions in VBA because I have never attempted to do this before. Can someone share with me how they would solve this to help give me some ideas? It is important that I apply the formatting using VBA and not using conditional formatting because I need the formatting to “stick” easily as it is copied and pasted to other worksheets.</SPAN>

So, let’s start with a simple example of what I am trying to do:</SPAN>

I have the following table:</SPAN>

Col A Col B</SPAN>
Company A 1.99 </SPAN>
Company B 2.99</SPAN>
Company C 0.00</SPAN>
Company D 0.00</SPAN>

If a company in the table has a price in Col b which is 0, then I want the color of the text for that cell to be changed to White. I also want this to happen for the cell to the left of that cell. </SPAN>

The next thing I would want to do is to change the background of the Cell in Col A that has the text “Company A” to Yellow and also make the same change to the Cell to the right of it.</SPAN>

Can someone help me out and point me in the right direction with some sample code?</SPAN>

 

[bbott]

Try something like this:

Sub CompCol()

    Dim x As Long
    
    For x = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("B" & x) > 0 Then
            Range("A" & x & ":B" & x).Interior.Color = vbYellow
        Else
            Range("A" & x & ":B" & x).Font.Color = vbWhite
        End If
    Next x
    
End Sub

 

[CharBram]

bbott,

Thanks very much for the code sample!!! It gave me a great place to start from and I then was able to modify it a bit to meet my needs. I actually have a lot of the tables I described that are right next to each other. So I needed code that would loop through that table and then move to the right in order to loop through the adjacent table.

I decided to use the offset function in order to achieve this because I couldn't think of a way to get VBA to move to the next column at the end of a loop. For instance, I can't simply change from column B to Column D by adding 2 at the end of the loop. Does this make sense?

Anyway, my code works as I need it to now, but let me know if there was a simpler/better/easier approach to doing this that you know of!

Modified Code Below:

Sub Cond_Format_VBA_Test2()
Dim x As Long
Y = 0

For i = 0 To 6 Step 2
    For x = 1 To Range("A" & Rows.Count).Offset(0, i).End(xlUp).Row
    
        If Range("B" & x).Offset(0, i) = 0 Then
            Range("A" & x & ":B" & x).Offset(0, i).Font.Color = vbWhite
        ElseIf Range("A" & x).Offset(0, i) = "Company A" Then
            Range("A" & x & ":B" & x).Offset(0, i).Interior.Color = vbYellow
            
        ElseIf Range("A" & x).Offset(0, i) = "Company B" Then
            Range("A" & x & ":B" & x).Offset(0, i).Interior.Color = vbBlue
            
        ElseIf Range("A" & x).Offset(0, i) = "Company C" Then
            Range("A" & x & ":B" & x).Offset(0, i).Interior.Color = vbRed
            
        End If
    Next x
Next i
End Sub

 

[bbott]

bbott,

Thanks very much for the code sample!!! It gave me a great place to start from and I then was able to modify it a bit to meet my needs. I actually have a lot of the tables I described that are right next to each other. So I needed code that would loop through that table and then move to the right in order to loop through the adjacent table.

I decided to use the offset function in order to achieve this because I couldn't think of a way to get VBA to move to the next column at the end of a loop. For instance, I can't simply change from column B to Column D by adding 2 at the end of the loop. Does this make sense?

Anyway, my code works as I need it to now, but let me know if there was a simpler/better/easier approach to doing this that you know of!

You're welcome.

As far as simpler/better/easier, that really depends on your comfort level and ability with VBA. I say if you're code works, then use it.

 

[CharBram]

Well, it works for now! So, I will continue to use this approach!

One thing that does confuse me, is the following code:

Rows.Count).Offset(0, i).End(xlUp).Row

What exactly is this doing? Counting the number of rows that have have data in them so it can know how long to loop?

Oh okay, I did a google search and it looks like this rows.count counts all the way to the last line of the spreadheet. Then it moves to the last non-blank row in that column. But how does it know what column I want it to look in?

 

[bbott]

I would actually change this:

For x = 1 To Range("A" & Rows.Count).Offset(0, i).End(xlUp).Row

to this:

For x = 1 To Range("A" & Rows.Count).End(xlUp).Row

This line is counting the number of rows used in column A. If, for instance, you wanted to count the rows in column C, it would be:

For x = 1 To Range("[COLOR=#ff0000][B]C[/B][/COLOR]" & Rows.Count).End(xlUp).Row

</pre>