Cant get VBA to change cell colour in loop

[Barbarte]

Hi again,

Here's my loop:

Do Until Cells(rowno14, 13) = ""

        If Cells(rowno14, 13).Value = "0.00" And Cells(rowno14, 14) = Cells(rowno14 + 1, 14) _
        And Cells(rowno14, 5) = Cells(rowno14 + 1, 5) Then
        
                Cells(rowno14, 13).Value = Zero
                
                Cells(rowno14, 13).Select
               
                Selection.Font.ColorIndex = 6
                With Selection.Interior
                    .ColorIndex = 3
                    .Pattern = xlSolid
                End With
                
                      
        Else
        
        If Cells(rowno14, 13).Value = "0" And Cells(rowno14, 5) <> Cells(rowno14 + 1, 5) _
        And Cells(rowno14, 5) <> Cells(rowno14 - 1, 5) Then
        
                Cells(rowno14, 13).Value = OneHundred
        
        End If
        End If

        rowno14 = rowno14 + 1
Loop

The loop works perfectly, does everything it's supposed to, EXCEPT change the colour of the cell and text when it's supposed to.

Any ideas??

Thanks,

Paul.

 

[Scott Huish]

It's not generally necessary to select cells in order to work with them, you can work with ranges directly in VBA.

But what is this supposed to do exactly?

Also, does the variable Zero contain 0? And the variable OneHundred contain 100, if so why not just Cells(rowno14,13)=0?

 

[Jonmo1]

Couldn't you use conditional formatting to change the Font and Cell Color?

Highlight column M, format - conditional formatting
formula is

=AND(M1=0,N1=N2,E1=E2)

Click Format
Adjust the Format as you wish
Click OK Twice.

Formula is written relative to row 1.

 

[Barbarte]

Hi Guys!

@ HOTPEPPER:

Yes they are 0 and 100
I didn't think cells(rowno, 13) = 0 would work!
do I not at least need "0" or is that only for text?

Anyway.
I'm very new to this whole programming thing, and I've only started using VBA directly in the past few weeks.
I did a 3 day course in VBA, so using stuff like "Set Zero As String" is as much to get that stuff into my head as it is to get the job done.
I'll get there eventually though.

The code searches through a list of song titles and copyright owners and checks who owns the song and what % share they own. if there is one owner and the share = 0 then it changes the share to 100 (for 100%).

Some shares show as 0 as a result of how the information is needed to be displayed to customers, but it's no use for our database.

But if there is more than one owner and a share of that = 0 then it leaves the cell value at 0 but changes the cell background colour so it's easy to spot.

Eventually, instead of changing the cell colour, I want it to sum the other copyright owners shares, and calculate what the value 0 should be so all owners shares add up to 100 (per song).
I haven't figured that bit out yet though, so changing the colour will do until I get it sorted.

@jonmo1:

I'll look into that.
will that remove the colour if the value changes from 0?

thanks,

Paul.

 

[Jonmo1]

yep, the cell (M1) will ONLY be colored IF

M1 = 0
AND
N1 = N2
AND
E1 = E2

If any of those 3 conditions are NOT met, the cell will not be colored.

 

[Barbarte]

I figured out what the problem was

This line:

If Cells(rowno14, 13).Value = "0.00" And Cells(rowno14, 14) = Cells(rowno14 + 1, 14) _ 
        And Cells(rowno14, 5) = Cells(rowno14 + 1, 5) Then

If I add this condition before the "Then" it checks the previous line as well as the next line and I get the correct result:

Or Cells(rowno14, 5) = Cells(rowno14 - 1, 5)

now if I can get it to sum the shares for each track and get it to put in a value that brings the total to 100 instead of changing the cell colour I'll be sorted! :wink:

Thanks!