Thread: macro to highlight blank cells in row if date in column A

Can anyone point me in the right direction to solve a blank cell problem. I need to create a macro to search all sheets in workbook to highlight entries where there is a date entered in a row in column A but no entry in columns E through M (1 entry in any of E through M would be ok)

If this data could be forwarded to a workbook with the cell B2 as a reference it would help greatly - I can sort that later

This is to seek occasions where work/material has been recorded if file but no cost/value entered

The attached file has two sheets where a yellow highlighted row will explain.

Assistance with any part of this would be greatly appreciated as I have trawled info for blank cell lookup but can nothing concerning mutiple columns

Ta Nigel

not answered yet -

can advance filter be used on a range ?? say Range= "E6:M195" or is it restricted to single columns

have you tried Conditional formatting?

[QUOTE=venkat1926;2640434]there are data in two sheets sheet1 and sheet 2. sheet 3 is renamed as "result"

Originally Posted by royUK

have you tried Conditional formatting?

Thanks for the replies, I will check venkat1926 code and see if can be used in a loop on multiple workbooks

royuk, highlight was probably the wrong word to use. I need to trawl the files everynow and again to find instances where data has been entered but no cost. If conditional formatting was used I would still have to scroll through every sheet to spot them. Cost can then be entered (purely an auditing exercise, boss doesn't care that we were never charged for it, he wants the cost entered anyway.

Will post results

@venkat1926

code works fine to gather instances into a "result sheet", would there be much involved in the cell "A1" or "B2", the registration being copied over to result sheet, so the instances can be identified and the worksheet return to to correct.

Am going to add code to a working file and see how it fairs

EDIT: code works no problem but files are shared and macro stalls at deleting "tentative" worksheet, i will look at turning off/on share. Each file also has a control sheet, can I add that to line ***

Option Explicit

Sub test()
Dim r As Range, c As Range, x As String, c1 As Range, j As Long, k As Long
Application.DisplayAlerts = False
Worksheets.Add
ActiveSheet.Name = "tentative"
Worksheets("result").Cells.Clear
For j = 1 To Worksheets.Count
' *** If Worksheets(j).Name = "result" Or Worksheets(j).Name = "tentative" Then GoTo nextj (add sheet control)
    With Worksheets(j).UsedRange
    .AutoFilter field:=1, Criteria1:=">0"
    Set r = .SpecialCells(xlCellTypeVisible)
     r.Copy Worksheets("tentative").Range("a1")
        With Worksheets("tentative").UsedRange
        For k = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
        'MsgBox k
        For Each c1 In Range(.Cells(k, "D"), .Cells(k, "M"))
        x = x & c1
        Next c1
       ' MsgBox x
        If x = "" Then
        .Cells(k, 1).EntireRow.Copy Worksheets("result").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                End If
                x = ""
            Next k
     End With
    Worksheets("tentative").Cells.Clear
    .AutoFilter
    End With
nextj:
  Next j
 'Worksheets("tentative").Delete
Application.DisplayAlerts = True
End Sub

made the changes to code to ignore the "control sheet" and open the "result" sheet before finish which is ideal. 2 things would make it useable

1. How can I change code so that when it finds an instance, it will copy either "A1" (or worksheet name) to the A column in the result worksheet so that the instance can be identified

2. How can I instruct the macro to delete the worksheet "tentative" in a shared file

This code can be copied into module2 in the attached workbook above

Option Explicit

Sub test()
Dim r As Range, c As Range, x As String, c1 As Range, j As Long, k As Long
Application.DisplayAlerts = False
Worksheets.Add
ActiveSheet.Name = "tentative"
Worksheets("result").Cells.Clear
For j = 1 To Worksheets.Count
If Worksheets(j).Name = "result" Or Worksheets(j).Name = "tentative" Or Worksheets(j).Name = "CONTROL SHEET" Then GoTo nextj
    With Worksheets(j).UsedRange
    .AutoFilter field:=1, Criteria1:=">0"
    Set r = .SpecialCells(xlCellTypeVisible)
     r.Copy Worksheets("tentative").Range("a1")
        With Worksheets("tentative").UsedRange
        For k = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
        'MsgBox k
        For Each c1 In Range(.Cells(k, "C"), .Cells(k, "K"))
        x = x & c1
        Next c1
       ' MsgBox x
        If x = "" Then
        .Cells(k, 1).EntireRow.Copy Worksheets("result").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                End If
                x = ""
            Next k
     End With
    Worksheets("tentative").Cells.Clear
    .AutoFilter
    End With
nextj:
  Next j
 Worksheets("tentative").Delete
 Worksheets("result").Select
Application.DisplayAlerts = True
End Sub

Macro test does what it is supposed to do now taking into account these are shared files

To make the macro useable it needs to reference the worksheet where all blank instances are copied to worksheet "result"

The code copies the entire row of instance where a blank is found, I could copy the sheetname as shown in worksheet "TR36" but that is not satisfactory. Code needs to copy with cell a1 over to column to right of data on worksheet "result" or the worksheet name

there are dynamic ranges refered to that I cannot seem to find and making code a bit of a mystery

Any help appreciated

Copy shared file attached

Where in code would insert command for every worksheet copy copy cell ("a1") to ("m1:m250") and then clear contents before moving onto next sheet. This would carry over sheet name when entire row is copied to sheets "result" and allow me to indentify it.

Any help appreciated

Option Explicit

Sub test()
Dim r As Range, c As Range, x As String, c1 As Range, j As Long, k As Long
Application.DisplayAlerts = False
Worksheets.Add
ActiveSheet.Name = "tentative"
Worksheets("result").Cells.Clear
For j = 1 To Worksheets.Count
If Worksheets(j).Name = "result" Or Worksheets(j).Name = "tentative" Or Worksheets(j).Name = "CONTROL SHEET" Then GoTo nextj


    With Worksheets(j).UsedRange
    .AutoFilter field:=1, Criteria1:=">0"
    Set r = .SpecialCells(xlCellTypeVisible)
     r.Copy Worksheets("tentative").Range("a1")
        With Worksheets("tentative").UsedRange
        For k = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
        'MsgBox k
        For Each c1 In Range(.Cells(k, "D"), .Cells(k, "K"))
        x = x & c1
        Next c1
       ' MsgBox x
        If x = "" Then
        .Cells(k, 1).EntireRow.Copy Worksheets("result").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
        
                End If
                x = ""

            Next k
     End With
    Worksheets("tentative").Cells.Clear
    .AutoFilter
    End With
nextj:
  Next j

    Application.DisplayAlerts = False

    ActiveWorkbook.ExclusiveAccess
  
 Worksheets("tentative").Delete
 


    If Not ActiveWorkbook.MultiUserEditing Then
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _
        accessMode:=xlShared
End If
    
    Application.DisplayAlerts = True
  
    

 Worksheets("result").Select

End Sub

any ideas on the code required to copy cell A1 to M1:M250 and delete again before macro moves on to next worksheet in workbook. When this unique reference is carried over to the "result" worksheet during the entire row copy it will allow me to identify where it came from.

Any help appreciated