Highlighting rows and using formatconditions and copy paste to different sheets

raseliba

New Member
Joined
Jun 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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#
 

Attachments

  • Capture.PNG
    Capture.PNG
    24.9 KB · Views: 7

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top