+ Reply to Thread
Results 1 to 2 of 2

Highlighting rows and using formatconditions and copy paste to different sheets

  1. #1
    Registered User
    Join Date
    06-12-2021
    Location
    Dhaka
    MS-Off Ver
    Microsoft 365
    Posts
    1

    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]
    Attached Files Attached Files
    Last edited by Raseliba; 06-13-2021 at 06:49 AM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,406

    Re: Highlighting rows and using formatconditions and copy paste to different sheets

    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!
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy , Insert rows and Paste data from two sheets to third sheet
    By narendra.tanmay in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-01-2020, 09:48 AM
  2. [SOLVED] How to Copy Rows from two Sheets and Paste it in 3rd Sheet One Bellow
    By MoldyBread in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-13-2018, 10:45 AM
  3. Copy rows from multiple sheets paste to new sheet
    By cindywylie in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-14-2017, 04:06 PM
  4. Copy and paste equal rows from master sheet to other sheets
    By Sasquatch2014 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2017, 09:42 AM
  5. [SOLVED] Copy, paste and insert new rows into new sheets
    By nanas in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-10-2012, 10:35 PM
  6. Copy/Paste Rows to Other Sheets, multiple conditions
    By financeguy57 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-17-2011, 01:39 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1