vba to check range of cells and print

[josh]

I need to write a macro that looks for any cells within a given range that
are font color red (255,0,0) and prompts the user with a vbyesno message box
asking them whether they want to print or not. If they choose no, exit sub.
if they choose yes, i have an inputbox asking them how many copies they want.
when they choose yes, i'm having trouble with the macro looping and
prompting them for every cell that is red. I only want them to be prompted
one time whether there is 1 red cell or 50. Any ideas?

 

[Jacob Skaria]

Josh, try the below. From workbook press Alt+F11 to launch VBE (Visual Basic
Editor). From the left treeview search for the workbook name and click on +
to expand it. Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim blnFound As Boolean, cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Font.ColorIndex = 3 Then blnFound = True: Exit For
Next
If blnFound Then
If MsgBox("Found highlighted cells. Print ?", vbYesNo + _
vbDefaultButton2) <> vbYes Then Cancel = True: Exit Sub
End If
End Sub

If this post helps click Yes

 

[josh]

Thanks for the response. I actually already have something very similar
written (that works), however I was trying to get the code tied to a button
on a particular worksheet versus having it as a workbook macro. There are
several worksheets within the file and I don't want my users to get prompted
if they aren't working on that particular worksheet.

 

[Jacob Skaria]

Fine. Try the below and attach that to a button.. and feedback

Sub Macro()
Dim blnFound As Boolean, cell As Range
Dim intTemp As Integer, intPrint As Integer

For Each cell In ActiveSheet.UsedRange
If cell.Font.ColorIndex = 3 Then blnFound = True: Exit For
Next

If blnFound Then
If MsgBox("Found highlighted cells. Print ?", vbYesNo + _
vbDefaultButton2) <> vbYes Then Exit Sub
End If

intPrint = InputBox("Please enter number of copies", , 1)
For intTemp = 1 To intPrint
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next intTemp

End Sub

If this post helps click Yes

 

[josh]

that got me close enough to figure it out. thank you.