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