Cell Color change with data input & print based on cell color

[smhutch]

I currently have a macro that prints all visible (unhidden) sheets.

Sub Button1_Click()
Dim ws As Worksheet
For Each ws In ThisWorkBook.Worksheets
If ws.Visible=xlSheetVisible Then ws.PrintOut
Next ws
End Sub

I want to have certain required fields be colored with a certain color. If data is input into that cell it changes colors.

Then when clicking the Print button I created, I want a macro to search all Visible Sheets and if there are any Orange Cells to 1.)Not Print 2.)Display Message Box indicating a required "cell" is missing data.

If cells in any Visible Sheet aren't that color, then I want it to proceed as written above.

Any Advice?

 

[lenze]

Hi and welcome to the Board!!
Why not just loop throught and check required cells for Content. I don't see the need to recolor them.

lenze

 

[smhutch]

I want to be able to easily display a required cell that is missing data.

 

[lenze]

OK:Here's how to Change the cell Color

Private Sub WorkSheet_Change(ByVal Target as Range)
Target.Interior.ColorIndex = 6 'Yellow Change to suit
End Sub

You will, of course, have to define the cell(s) or Range of cells in your code where you want this to apply, else it will be triggered for every cell

lenze

 

[smhutch]

My apologies, I should have inserted on my last reply the functionality with the print button.

I want the print button when clicked to print all visible sheets only if there are no cells that are orange on the visible sheets. If there are orange cells, I don't want it to print and I want it to display a message box that indicates "Please Check to make sure all required information is input."

If there are no orange cells, it should just then print all visible worksheets.

My apologies if my initial question and follow up were not more direct.

I do appreciate all the help!

 

[lenze]

Totally untested

Sub Button1_Click()
Dim ws As Worksheet
Dim cl as Range
For Each ws In ThisWorkBook.Worksheets
     If ws.Visible=xlSheetVisible Then
          For each cl in ws.UsedRange
              If cl.Interior.ColorIndex = 35 Then
                MsgBox "Check for missing data on Sheet " & ws.Name"
                Exit Sub
          Else: ws.PrintOut
          End If
      End If
Next ws
End Sub

lenze