Macro colors a cell that doesn't meet the criteria of the macro

My macro seems to find data that isnt there (IE from blank cells).
The macro checks a column on sheet4 and if it is blank, do nothing, else color a font red in corrosponding cell on sheet3.

I cant figure out why it colors a cell that doesn't meet the criteria of the macro. In particular is one set of data on sheet 4 (column C) with contents of "BM#10-1" (BM#10-1, -2 , etc). The column labeled "exceed" (Column L) is blank for each entry. Yet the macro will color the text on sheet3 (red) as if it found data in column L.
This "BM#10-x" is correlated to sheet3 column A "5. Char No.". (10)

This is the macro.

Please Login or Register  to view this content.

Attached is a workbook (no macros).

Any hints, tips or examples is appreciated.

It seems obvious: never use merged cells when using VBA.
Your problems will disappear once you have removed all merged cells.


You can also reduce the whole of your code to:

Please Login or Register  to view this content.

NB don't forget to disable 'option explicit'

Originally Posted by rylo

Hi

Given the volume of activity, can you give a specific row example in sheet4 of where things are going wrong, and where on sheet3 things are being colored where they shouldn't for that item.

rylo

See attached images that relate to my ramblings of my original post.

I cant figure out why it colors a cell that doesn't meet the criteria of the macro. In particular is one set of data on sheet 4 (column C) with contents of "BM#10-1" (BM#10-1, -2 , etc). The column labeled "exceed" (Column L) is blank for each entry. Yet the macro will color the text on sheet3 (red) as if it found data in column L.
This "BM#10-x" is correlated to sheet3 column A "5. Char No.". (10)

snb
I wish I could get away from merged cells, but that is not an option for me.

Did you check the code I provided ? It avoids the problems created by merged cells.

I get an error

Please Login or Register  to view this content.

Please Login or Register  to view this content.

from

Please Login or Register  to view this content.

Check the code step by step. F8

I counted the loops until it generated the error, on the 66th loop it errors.

The watch value of "sp(1)" = "31-1" at the time it errors. (not sure what that is telling me)
The watch value of sp(1) is 179 all other times.

That means that value is not found. Maybe add some error handling and variable declarations...

Yea, I can not find "31-1" on any sheet.

Edit.
There is a #31-1, etc. (I had the search options set wrong).

In the file you posted in sheet 'sheet3' column A I don't find a value '31-1'.
If you can find it in your workbook, please check whether that cell contains only '31-1' (no spaces or invisible characters).

Column C contains that value (#31-1, #31-2, etc.).
Column C contains all the numeric values for comparing data.

Correct me if im wrong, but your code is looking at column C.

Please Login or Register  to view this content.

The row the error is generated is 68 (I miss counted before).
Below is the data of row 68 from column A thru L

Please Login or Register  to view this content.

Im not sure why the macro is failing at that point.

Because that macro is looking for that value in sheet3, column A.
Column A in sheet3 doesn't contain the value '31-1'.

As you may well be aware of:
Sheets("Sheet3").Cells(7, 1) is equivalent to
Sheets("Sheet3").Range("A7")

Please read 1 VBA-line further down:
If UBound(sp) > 0 Then Sheets("Sheet3").Cells(7, 1).Resize(Sheets("sheet3").UsedRange.Rows.Count).Find(sp(1), , xlValues, xlWhole).Offset(, 4).Font.Color = -16776961

Oh...I see.
At least I know where the problem is, that can get me going on a fix.