Can someone help me figure out why this macro is skipping some cells (Conditional Formatting)?

C

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
496
Office Version
  1. 365
Platform
  1. Windows
Hello so I have a variable range similar to this one:

Debits(21450).xlsx
ABCDEFGHI
1
2
3Heading
4xxxx
5xxxx
6
7
8NYCBronx2/11/2022Jan-22P-1041App070.14220.00
9NYCBronx2/12/2022Jan-22P-1042App0110.150.00
10NYCBronx2/13/2022Jan-22P-1043App0150.350.00
11NYCBronx2/14/2022Jan-22P-1044App0500.350.00
12NYCBronx2/15/2022Jan-22P-1045App0176.550.00
13NYCBronx2/16/2022Jan-22P-1046App0157.140.00
14NYCBronx2/17/2022Jan-22P-1047App0927.940.00
15NYCBronx2/18/2022Jan-22P-1048App0121.800.00
16NYCBronx2/19/2022Jan-22P-1049App0271.770.00
17NYCBronx2/20/2022Jan-22P-1050App031.230.00
18NYCBronx2/21/2022Jan-22P-1051App070.000.00
19NYCBronx2/22/2022Jan-22P-1052App01,500.500.00
20NYCBronx2/23/2022Jan-22P-1053App070.140.00
21NYCBronx2/24/2022Jan-22P-1054App0110.150.00
22NYCBronx2/25/2022Jan-22P-1055App0150.350.00
23NYCBronx2/26/2022Jan-22P-1056App0500.350.00
24NYCBronx2/27/2022Jan-22P-1057App0176.550.00
25NYCBronx2/28/2022Jan-22P-1058App0157.140.00
26NYCBronx3/1/2022Jan-22P-1059App0927.940.00
27NYCBronx3/2/2022Jan-22P-1060App0121.800.00
28NYCBronx3/3/2022Jan-22P-1061App0271.770.00
29NYCBronx3/4/2022Jan-22P-1062App031.2370.14
30NYCBronx3/5/2022Jan-22P-1063App070.00110.15
31NYCBronx3/6/2022Jan-22P-1064App01,500.50150.35
32NYCBronx3/7/2022Jan-22P-1065App00.00500.35
33NYCBronx3/8/2022Jan-22P-1066App00.00176.55
34NYCBronx3/9/2022Jan-22P-1067App00.00157.14
35NYCBronx3/10/2022Jan-22P-1068App00.00927.94
36NYCBronx3/11/2022Jan-22P-1069App00.00121.80
37NYCBronx3/12/2022Jan-22P-1070App00.00271.77
38NYCBronx3/13/2022Jan-22P-1071App00.0031.23
39NYCBronx3/14/2022Jan-22P-1072App00.0070.00
40NYCBronx3/15/2022Jan-22P-1073App00.001,500.50
41NYCBronx2/24/2022Jan-22P-1054App00.00110.15
42NYCBronx2/25/2022Jan-22P-1055App00.00150.35
43NYCBronx2/26/2022Jan-22P-1056App00.00500.35
44NYCBronx2/27/2022Jan-22P-1057App00.00176.55
45NYCBronx2/28/2022Jan-22P-1058App00.00157.14
46NYCBronx3/1/2022Jan-22P-1059App00.00927.94
47NYCBronx3/2/2022Jan-22P-1060App00.00121.80
48NYCBronx3/3/2022Jan-22P-1061App00.00271.77
Report1


I tailored a macro I found here to work for me and I came up with this
VBA Code: 
Sub DebitsNCredits()
  Dim c As Range, r As Range, f As Range, n As Long, cell As String
  n = 8
  Set r = Range("H8", Range("H" & Rows.Count).End(3))
  r.Interior.ColorIndex = xlNone
  For Each c In Range("I8", Range("I" & Rows.Count).End(3))
    If c.Value <> "" Then
      Set f = r.Find(c, , xlValues, xlWhole)
      If Not f Is Nothing Then
        cell = f.Address
        Do
          If f.Interior.ColorIndex = xlNone Then
            f.Interior.ColorIndex = n
            c.Interior.ColorIndex = n
            n = n + 1
            If n = 56 Then n = 8
            Exit Do
          End If
          Set f = r.FindNext(f)
        Loop While Not f Is Nothing And f.Address <> cell
      End If
    End If
  Next
End Sub

But for some reason this code is skipping some numbers? Any idea as to what is causing this? Or how I can fix it?

Debits(21450).xlsx
ABCDEFGHI
1
2
3Heading
4xxxx
5xxxx
6
7
8NYCBronx2/11/2022Jan-22P-1041App070.14220.00
9NYCBronx2/12/2022Jan-22P-1042App0110.150.00
10NYCBronx2/13/2022Jan-22P-1043App0150.350.00
11NYCBronx2/14/2022Jan-22P-1044App0500.350.00
12NYCBronx2/15/2022Jan-22P-1045App0176.550.00
13NYCBronx2/16/2022Jan-22P-1046App0157.140.00
14NYCBronx2/17/2022Jan-22P-1047App0927.940.00
15NYCBronx2/18/2022Jan-22P-1048App0121.800.00
16NYCBronx2/19/2022Jan-22P-1049App0271.770.00
17NYCBronx2/20/2022Jan-22P-1050App031.230.00
18NYCBronx2/21/2022Jan-22P-1051App070.000.00
19NYCBronx2/22/2022Jan-22P-1052App01,500.500.00
20NYCBronx2/23/2022Jan-22P-1053App070.140.00
21NYCBronx2/24/2022Jan-22P-1054App0110.150.00
22NYCBronx2/25/2022Jan-22P-1055App0150.350.00
23NYCBronx2/26/2022Jan-22P-1056App0500.350.00
24NYCBronx2/27/2022Jan-22P-1057App0176.550.00
25NYCBronx2/28/2022Jan-22P-1058App0157.140.00
26NYCBronx3/1/2022Jan-22P-1059App0927.940.00
27NYCBronx3/2/2022Jan-22P-1060App0121.800.00
28NYCBronx3/3/2022Jan-22P-1061App0271.770.00
29NYCBronx3/4/2022Jan-22P-1062App031.2370.14
30NYCBronx3/5/2022Jan-22P-1063App070.00110.15
31NYCBronx3/6/2022Jan-22P-1064App01,500.50150.35
32NYCBronx3/7/2022Jan-22P-1065App00.00500.35
33NYCBronx3/8/2022Jan-22P-1066App00.00176.55
34NYCBronx3/9/2022Jan-22P-1067App00.00157.14
35NYCBronx3/10/2022Jan-22P-1068App00.00927.94
36NYCBronx3/11/2022Jan-22P-1069App00.00121.80
37NYCBronx3/12/2022Jan-22P-1070App00.00271.77
38NYCBronx3/13/2022Jan-22P-1071App00.0031.23
39NYCBronx3/14/2022Jan-22P-1072App00.0070.00
40NYCBronx3/15/2022Jan-22P-1073App00.001,500.50
41NYCBronx2/24/2022Jan-22P-1054App00.00110.15
42NYCBronx2/25/2022Jan-22P-1055App00.00150.35
43NYCBronx2/26/2022Jan-22P-1056App00.00500.35
44NYCBronx2/27/2022Jan-22P-1057App00.00176.55
45NYCBronx2/28/2022Jan-22P-1058App00.00157.14
46NYCBronx3/1/2022Jan-22P-1059App00.00927.94
47NYCBronx3/2/2022Jan-22P-1060App00.00121.80
48NYCBronx3/3/2022Jan-22P-1061App00.00271.77
Report1
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Sorry but can't see any skipped numbers in your list, what rimains uncolored are only single numbers.
 
Upvote 0
If we look at what we see in your post, yes they should, but I copied your data and macro in my workbook and once run the macro the result is completely different.

Screenshot 2022-02-12 112206.jpg
 
Upvote 0
rollis13 said:
If we look at what we see in your post, yes they should, but I copied your data and macro in my workbook and once run the macro the result is completely different.

View attachment 57652
Click to expand...
Thank you. Yes I noticed that in mine the 0 were not being highlighted as well for some reason. I have no idea what is causing this. Any ideas?
 
Upvote 0
rollis13 said:
If we look at what we see in your post, yes they should, but I copied your data and macro in my workbook and once run the macro the result is completely different.

View attachment 57652
Click to expand...
Oddly enough, when I copied the data from my post I got your same results..... The original source data is not giving me the same results though.

Edit: I think I figured it out, I removed all the formatting(merged and center etc) and ensured both columns were formatted as numbers. That seems to have fixed the issue.
 
Last edited:
Upvote 0
Could be rounding errors?
In a vacant cell in your original file, put this formula & check the result.
=EXACT(H15,I36)
 
Upvote 0
Probably because some aren't formatted the same way. If I format your data 'Number with 2 decimals' I find that some are text but I'm copying your data after a XL2BB elaboration.

PS: saw now the new posts.
 
Upvote 0
1644665411361.png


Peter_SSs said:
Could be rounding errors?
In a vacant cell in your original file, put this formula & check the result.
=EXACT(H15,I36)
Click to expand...

Here is what I have, sorry could not use XL2BB on this laptop. What is the significance or the meaning behind the true and false values?
 
Upvote 0
rollis13 said:
Probably because some aren't formatted the same way. If I format your data 'Number with 2 decimals' I find that some are text but I'm copying your data after a XL2BB elaboration.

PS: saw now the new posts.
Click to expand...
Thank you. I meant to say that if they were formatted as General they would work. All working smoothly now.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Solved
Adding a new time causes wrong average
Replies
3
Views
157
awoohaw
awoohaw
K
  • Question
conditional formatting question
Replies
2
Views
224
kerm007
K
MartinS
  • Question
Update FormatConditions Shifts Column/Row Reference
Replies
0
Views
74
MartinS
MartinS
zookeepertx
  • Question
Having trouble "deciphering" some code so I can see how it works
Replies
7
Views
128
zookeepertx
zookeepertx
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
417
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,989
Messages
6,128,149
Members
449,427
Latest member
jahaynes

Share this page

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
Back
Top