Hello good people,
I am working on a database where people join and have an ID card. ID cards are expired. I want to write VBA codes to highlights an entire row based on different cell values and then copy and paste those values to different sheets according to highlighted rows.
A snap is attached herewith for your understanding. Is there any better ways to combine all the requirements? Please suggest and help.
Thanks in advance.
I tried these two codes:
#Code:1
#Sub CopyPasteHighlightedCells()
Dim StaffIDField As Range
Dim StaffIDCell As Range
Dim AllStaffsWS As Worksheet
Dim RActionWS As Worksheet
Dim FActionWS As Worksheet
Set AllStaffsWS = Worksheets("All Staffs")
Set StaffIDField = AllStaffsWS.Range("A2", AllStaffsWS.Range("A2").End(xlDown))
Set RActionWS = Worksheets("Required Action")
Set FActionWS = Worksheets("Fast Action")
For Each StaffIDCell In StaffIDField
If StaffIDCell.Interior.Color = RGB(255, 0, 0) Then
StaffIDCell.Resize(1, 9).Copy Destination:= _
FActionWS.Range("A1").Offset(FActionWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
End If
If StaffIDCell.Interior.Color = RGB(255, 255, 0) Then
StaffIDCell.Resize(1, 9).Copy Destination:= _
HActionWS.Range("A1").Offset(FActionWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
End If
Next StaffIDCell
FActionWS.Columns.AutoFit
RActionWS.Columns.AutoFit
End Sub
Sub Highlighting_Cells()
Dim cond1 As FormatCondition
Dim cond2 As FormatCondition
Dim cond3 As FormatCondition
Dim rg As Range
Dim last As Long
last = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Set rg = Range("A2:J" & last)
With rg
.FormatConditions.Delete
Set cond1 = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=ISEMPTY($J5)")
cond1.StopIfTrue = True
Set cond2 = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=AND($J5>30,$J5<46)")
cond2.StopIfTrue = True
Set cond3 = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=$J5<=30")
cond3.StopIfTrue = True
With .FormatConditions(1)
.Interior.Color = xlNone
.Font.Color = xlNone
End With
With .FormatConditions(2)
.Interior.Color = RGB(255, 255, 0)
.Font.Color = vbBlack
End With
With .FormatConditions(3)
.Interior.Color = RGB(255, 0, 0)
.Font.Color = vbBlack
End With
End With
End Sub#
I am working on a database where people join and have an ID card. ID cards are expired. I want to write VBA codes to highlights an entire row based on different cell values and then copy and paste those values to different sheets according to highlighted rows.
A snap is attached herewith for your understanding. Is there any better ways to combine all the requirements? Please suggest and help.
Thanks in advance.
I tried these two codes:
#Code:1
#Sub CopyPasteHighlightedCells()
Dim StaffIDField As Range
Dim StaffIDCell As Range
Dim AllStaffsWS As Worksheet
Dim RActionWS As Worksheet
Dim FActionWS As Worksheet
Set AllStaffsWS = Worksheets("All Staffs")
Set StaffIDField = AllStaffsWS.Range("A2", AllStaffsWS.Range("A2").End(xlDown))
Set RActionWS = Worksheets("Required Action")
Set FActionWS = Worksheets("Fast Action")
For Each StaffIDCell In StaffIDField
If StaffIDCell.Interior.Color = RGB(255, 0, 0) Then
StaffIDCell.Resize(1, 9).Copy Destination:= _
FActionWS.Range("A1").Offset(FActionWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
End If
If StaffIDCell.Interior.Color = RGB(255, 255, 0) Then
StaffIDCell.Resize(1, 9).Copy Destination:= _
HActionWS.Range("A1").Offset(FActionWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
End If
Next StaffIDCell
FActionWS.Columns.AutoFit
RActionWS.Columns.AutoFit
End Sub
Sub Highlighting_Cells()
Dim cond1 As FormatCondition
Dim cond2 As FormatCondition
Dim cond3 As FormatCondition
Dim rg As Range
Dim last As Long
last = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Set rg = Range("A2:J" & last)
With rg
.FormatConditions.Delete
Set cond1 = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=ISEMPTY($J5)")
cond1.StopIfTrue = True
Set cond2 = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=AND($J5>30,$J5<46)")
cond2.StopIfTrue = True
Set cond3 = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=$J5<=30")
cond3.StopIfTrue = True
With .FormatConditions(1)
.Interior.Color = xlNone
.Font.Color = xlNone
End With
With .FormatConditions(2)
.Interior.Color = RGB(255, 255, 0)
.Font.Color = vbBlack
End With
With .FormatConditions(3)
.Interior.Color = RGB(255, 0, 0)
.Font.Color = vbBlack
End With
End With
End Sub#