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:

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

[/#]

Quote from royUK

Why is it necessary to move data? It's a pointless exercise when you highlight the rows that need action

Your conditional formatting should stat in Row 5 not 2.

I want to highlight rows according to 4 conditions based on values on column J.

a. 45 and above - no filtered color

b. between 30 and 45 - yellow color

c. less than or equal to 30 - Red color

4. any blank cell - no filtered color

Here I exclude a condition and tried to write code for b, c, and d. These highlighted rows will be copy and pasted to the next two tabs.

yellow ones will be pasted in ''Required Action'' and red rows will be pasted in ''Fast Action'' maintaining the resizing formatting.

The problem I faced: If I generally filled any row with red or yellow color, copy-paste function works. If I use conditional formatting from excel, rows are nicely highlighted, BUT.... copy paste is not working on the highlighted rows already by using excel conditional formatting from the HOME tab.

So I thought to write another code for formatconditions as well. I need to merge both vba sub-functions to make it smooth.

Can you help?

Quote from royUK

Have you read the Forum Rules yet?

Again, I see no point in copying data to multiple sheets.

I am struggling to use the code.
I need to copy to multiple sheets to separate this ID-based information and print out.
As you can see, all these IDs will expire soon (dates are just assumptions, not actual ones).

Thanks.

Quote from royUK

Have you read the Forum Rules yet?

Again, I see no point in copying data to multiple sheets.

Can you suggest to get the 'edit' option of my thread?

Quote from royUK

I've added code to filter and print each condition apart from empty cells because I don't understand what you mean.

The code currentl previews but you can delete that line and use the PrintOut line

Thank you!
This simple coding makes sense. But the main conditions were missing.

I want to highlight entire rows according to 4 conditions based on the cell values on column 'G'.

a. 45 and above - no filtered color

b. between 30 and 45 - yellow color

c. less than or equal to 30 - Red color

4. any blank cell - no filtered color

These highlighted rows will be copied and pasted to the multiple sheets for later review with a single click. If I require it, I will print it. Therefore, I can see immediately which Staff IDs are yet to renew. Yellow ones will be pasted in ''Required Action'' and Red rows will be pasted in ''Fast Action'' maintaining the resizing formatting.

I hope, I could make the idea clear.
Thanks again.