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

C

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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
271
awoohaw
awoohaw
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
251
etaf
etaf
K
  • Question Question
conditional formatting question
Replies
2
Views
563
kerm007
K
F
  • Question Question
Changing date format based on fiscal years versus calendar years
Replies
2
Views
161
Peter_SSs
Peter_SSs
S
  • Solved
Individual Ranking
2
Replies
11
Views
433
sksanjeev786
S

Forum statistics

Threads
1,223,099
Messages
6,170,109
Members
452,302
Latest member
TaMere

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