Highlighting rows and using formatconditions and copy paste to different sheets

[raseliba]

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#

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Highlighting rows and using formatconditions and copy paste to different sheets - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[raseliba]

Alright. I will follow the rule.
Thanks.

 

[raseliba]

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.

Is there any way to write the code? thanks.