Home > Net >  Need to color empty cells in a specific columns in a workbook contain multiple sheets
Need to color empty cells in a specific columns in a workbook contain multiple sheets

Time:07-25

Okay, I've spent way too long on this problem so I'm looking for a little help again because it's become painfully obvious that I'm an idiot.

I have a workbook that has multiple sheets, the first sheet is the "Dashboard" containing buttons used to execute the macro, and the second sheet and all other sheets (3,4,...) have the same headlines. I need to search for the empty Cells in columns A & C in all the sheets and colorize them. And if possible copy the results in another sheet name (Result). Ps: the sheet's name is different, and changed based on the files browsed.

Any assistance at all will be greatly appreciated and will halt my inexorable slide into Excel-induced madness.

CodePudding user response:

I'm so sorry for the issues I did when I ask you, sirs, This is the Code that I write so far

'------- Choose the folder where your files -----------

    Private Sub CommandButton1_Click()
     Dim Path As String
     Dim Filename As String
     Dim Sheet As Worksheet
     Dim Name_Sheet As String
    Application.ScreenUpdating = False
    Call CommandButton2_Click
     Path = TextBox1.Value   "\"
     Filename = Dir(Path & "*.xls*")
     Do While Filename <> ""
     Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     
     For Each Sheet In ActiveWorkbook.Sheets
     If Sheet.Name = "Cumulated BOM" Then
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
     End If
     Next Sheet
     Sheets("Cumulated BOM").Name = Left(Filename, 20)
     Workbooks(Filename).Close
     Filename = Dir()
    
    Loop
    Application.ScreenUpdating = True
    
     Worksheets("Dashbord").Activate
     
    
    End Sub

'--------------Colorize empty cells ----------

    Sub empty_cells()

   Dim r, c, cnt
   r = 1
   c = 1
   cnt = 0

   For c = 1 To 14
   For r = 1 To 20
   If IsEmpty(Cells(r, c).Value) = True Then
   Cells(r, c).Interior.Color = vbYellow
   cnt = cnt   1
   End If
   Next r

   Next c

 
   End Sub

CodePudding user response:

This is another try I get from the Internet

 Private Sub CommandButton3_Click()

Application.ScreenUpdating = False

Dim rng As Range

Set rng = Selection

rng.SpecialCells(xlCellTypeBlanks).Interior.Color = vbYellow


Application.ScreenUpdating = True

End Sub
  • Related