Cant get VBA to change cell colour in loop

Barbarte

Board Regular
Joined
Dec 13, 2006
Messages
125
Hi again,

Here's my loop:

Code:
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Couldn't you use conditional formatting to change the Font and Cell Color?

Highlight column M, format - conditional formatting
formula is

Code:
=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

Board Regular
Joined
Dec 13, 2006
Messages
125
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. :biggrin:

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Dec 13, 2006
Messages
125
I figured out what the problem was :cool:

This line:

Code:
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:

Code:
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,100
Members
425,453
Latest member
bince

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top