Highlighting rows and using formatconditions and copy paste to different sheets

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
[INDENT]

[/#]
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

[/#]
[INDENT]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

---

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!