vba to check range of cells and print
 

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?

vba to check range of cells and print
 

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
---------------
Jacob Skaria


"josh" wrote:

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?

vba to check range of cells and print
 

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.

vba to check range of cells and print
 

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
---------------
Jacob Skaria


"josh" wrote:

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.

vba to check range of cells and print
 

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