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

Coyotex3

Well-known Member
Joined
Dec 12, 2021
Messages
507
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
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
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
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
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


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

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
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.
Thank you. I meant to say that if they were formatted as General they would work. All working smoothly now.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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