+ Reply to Thread
Results 1 to 2 of 2

Code Check Please, Highlights based of values

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-16-2016
    Location
    Colorado, US
    MS-Off Ver
    2013
    Posts
    115

    Code Check Please, Highlights based of values

    Hey Everyone for some reason im getting a next without a for error in the debug for this code:

    
    Sub BondsMod()
    
    Dim Nedas
    Dim nedaCol As Range
    Dim costCol As Range
    Dim i As Long
    Dim costR As String
    Dim costY As String
    Dim costYe As String
    Dim LastRow As Long
    Dim LastCol As Long
    Dim WS As Worksheet
    
    
        'Setting variables for modifcations
        
        Set WS = ActiveSheet
    
        LastRow = WS.Range("A" & Rows.Count).End(xlUp).Row
        LastCol = WS.Cells(1, Columns.Count).End(xlToLeft).Column
        costY = "$5,000.00"
        costYe = "$9,999.99"
        costR = "$10,000.00"
        Set nedaCol = WS.Range("A1").EntireRow.Find("Neda")
        Set costCol = WS.Range("A1").EntireRow.Find("(Bond Qty)")
        
        'This will check to verify required columns exist
            If nedaCol Is Nothing Then
                MsgBox ("The header ""Neda"" was not found, program will stop.")
                Exit Sub
            End If
            If costCol Is Nothing Then
                MsgBox ("The header ""Extended Cost (Bond Qty)"" was not found, program will stop.")
                Exit Sub
             End If
             
        For i = 2 To LastRow
                'if the cost column exists and the value is between 5000 and 9999 we highlight the row yellow, if the value is greater than 9999 we highlight the row red
            If WS.Cells(i, costCol.Column).Value >= costY And WS.Cells(i, costCol.Column).Value <= costYe Then
                    WS.Range(Cells(i, 1), Cells(i, LastCol)).Interior.Color = RGB(255, 255, 0)
            End If
            If Not nedaCol Is Nothing Then
                'if Neda column exists and the array of Nedas contains the one in the column, then we make the row red
                Nedas = Array("1037", "1755", "1077", "2596", "2406", "2589", "2587", "5596", "5401", "5403", "5559", "7401", "7650", "7447", "7501", "7433", "6733", "6484", "7432", "9183")
            If UBound(Filter(statuses, WS.Cells(i, nedaCol.Column).Value)) > -1 Then
                WS.Range(Cells(i, 1), Cells(i, LastCol)).Interior.Color = RGB(255, 0, 0)
            End If
        Next i
            
    End Sub
    I was trying to practice with some things I learned from on of the forum experts but im not sure why im getting this error (or if the variables I have set for cost will work) trying o have it search for currency values

    Thanks as always to those greater and wiser than I

    Fawkes_

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Code Check Please, Highlights based of values

    Double post from https://www.excelforum.com/showthread.php?t=1173137
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

+ 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. Code highlights additional activecell in addition to what it has to highlight
    By subbby in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-30-2015, 02:42 PM
  2. VBA Code Needed That Highlights All Differences Between Two Iterations of Same File
    By Aimee S. in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-29-2014, 11:36 AM
  3. [SOLVED] How to toggle on/off an event code which highlights active row and column?
    By blaque in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-09-2013, 11:56 AM
  4. Code that highlights a row
    By reignkystar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2013, 09:55 PM
  5. [SOLVED] VBA code to disable check boxes based on values
    By Chris Shepherd in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-23-2013, 12:48 AM
  6. spreadsheet code that highlights the row a cell is in and then the 3 rows bellow it?
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2012, 07:51 AM
  7. How to change existing code that highlights duplicates?
    By simonwilliams in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2009, 10:47 AM

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