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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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