Solved Highlight cell on print (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 04:20
Joined
Jun 26, 2007
Messages
856
Hello, I have a Excell sheet that I use to print either 5 days or 4 days worth of copy's using a button on sheets heading. Anyways I need the VBA on the 5th print (Button Print 5 Days) which the date would be end on a Friday to highlight cell E7 for that print then change tit back to normal. How can I do this?

Attached is the workbook/sheet
 

Attachments

  • test.zip
    20.3 KB · Views: 57

Gasman

Enthusiastic Amateur
Local time
Today, 09:20
Joined
Sep 21, 2011
Messages
14,607
Keep track of the print number.
Set the cell backcolour when on 5
Print the last copy
Unset the cell backcolour or do not save the workbook.
 
Last edited:

oxicottin

Learning by pecking away....
Local time
Today, 04:20
Joined
Jun 26, 2007
Messages
856
Keep track of the print number.
Set the cell backcolour when on 5
Print the last copy
Unset the cell backcolour or do not save the workbook.
@Gasman this works but is there a better way?

Code:
Sub Test()
    
    Dim x As Integer
    
    With Sheets("Plant Attendance")
        For x = 1 To 5
            If x = 5 Then
                .Range("E7").Select
                Selection.Interior.Color = vbRed
                .PrintOut
                Selection.Interior.Color = vbWhite
            Else
                .PrintOut
                .Range("E7") = .Range("E7") + 1
            End If
            Next x
            
        End With
        
'Increment
        ActiveSheet.Range("E7").Value = ActiveSheet.Range("E7").Value + 2
        ActiveSheet.Calculate
    End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:20
Joined
Sep 21, 2011
Messages
14,607
Yes, probably not saving the workbook?
Code:
Sub Test()
    
    Dim x As Integer
    
    With Sheets("Plant Attendance")
        For x = 1 To 5
            If x = 5 Then
                .Range("E7")..Interior.Color = vbRed
             End If
                .PrintOut
            Next x
        End With
        
'Increment
        ActiveSheet.Range("E7").Value = ActiveSheet.Range("E7").Value + 2
        ActiveSheet.Calculate
    End Sub
 

oxicottin

Learning by pecking away....
Local time
Today, 04:20
Joined
Jun 26, 2007
Messages
856
Yes, probably not saving the workbook?
Code:
Sub Test()
  
    Dim x As Integer
  
    With Sheets("Plant Attendance")
        For x = 1 To 5
            If x = 5 Then
                .Range("E7")..Interior.Color = vbRed
             End If
                .PrintOut
            Next x
        End With
      
'Increment
        ActiveSheet.Range("E7").Value = ActiveSheet.Range("E7").Value + 2
        ActiveSheet.Calculate
    End Sub

Yes im saving the workbook so I always have the last date used in my cell E7, Below is better looking VB?

Code:
Sub Test()
   
    Dim x As Integer
   
    With Sheets("Plant Attendance")
        For x = 1 To 5
           
            If x = 5 Then
                .Range("E7").Interior.ColorIndex = 35  'Magenta
                .PrintOut
                .Range("E7").Interior.ColorIndex = -4142  ' Interior.ColorIndex = -4142 is enumeration to clear the background or fill color.
            Else
                .PrintOut
                .Range("E7") = .Range("E7") + 1
            End If
            Next x
        End With
       
'Increment
        ActiveSheet.Range("E7").Value = ActiveSheet.Range("E7").Value + 2
        ActiveSheet.Calculate
        ActiveSheet.Save
    End Sub
 

Users who are viewing this thread

Top Bottom