Highlight entire row based on cell value

Originally Posted by JBeaucaire

Can you post up your macro (don't forget to put code tags around the code) and point out IN the macro where you would want this evaluation to occur? There are many ways to do this and how you're working through your sheet will affect the answer(s).

Sub SPGBreaksheet()
'
' SPGBreaksheet Macro
'

'
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("TRECS").Select
    Sheets("TRECS").Move After:=Sheets(7)
    ActiveWindow.Zoom = 85
    Range("I:I,L:L,M:M,N:N").Select
    Range("N1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("F:H,K:K").Select
    Range("K1").Activate
    Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    Cells.Select
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste
    ActiveWindow.Zoom = 85
    Range("A1:K173").Select
    Range("D1").Activate
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.Columns.AutoFit
    Sheets("Sheet1").Name = "Position"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.Columns.AutoFit
    ActiveWindow.Zoom = 85
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.Columns.AutoFit
    ActiveWindow.Zoom = 85
    Sheets("Sheet3").Name = "AS"
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.Columns.AutoFit
    ActiveWindow.Zoom = 85
    Sheets("Sheet4").Name = "Yesterday"
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveSheet.Next.Select
    ActiveSheet.Previous.Select
    Range("C1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Paste
    ActiveWindow.Zoom = 85
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.Columns.AutoFit
    Sheets("Sheet5").Name = "CCY"
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Paste
    ActiveWindow.Zoom = 85
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.Columns.AutoFit
    Sheets("Sheet6").Name = "Futures"
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    Sheets("Sheet2").Name = "CA"
    ActiveSheet.Previous.Select
    ActiveWorkbook.Worksheets("Position").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Position").AutoFilter.Sort.SortFields.Add Key:= _
        Range("E1:E173"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Position").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
  Dim Data, del As Range
Dim Cll As Excel.Range
Dim m As Long
Dim n As Long

Range(Cells(1, 5), Cells(Rows.Count, 5).End(xlUp)).Select
Set Data = Excel.Selection
    For Each Cll In Data.Cells
        If Left(Cll.Value, 3) = "995" Then
        Cll.EntireRow.Cut
        ActiveSheet.Paste Sheets("CCY").Range("A65536").End(xlUp).Offset(1, 0)
        End If
     Next Cll
   
Range(Cells(1, 5), Cells(Rows.Count, 5).End(xlUp)).Select
Set Data = Excel.Selection
    For Each Cll In Data.Cells
        If Left(Cll.Value, 3) = "9FF" Then
        Cll.EntireRow.Cut
        ActiveSheet.Paste Sheets("Futures").Range("A65536").End(xlUp).Offset(1, 0)
        End If
     Next Cll
     
  
End Sub

I want it to come right before end sub or in other words I want it to be the last thing to happen. Also in that same section, I am wondering if it finds CRL in column C it will Italicize the entire row independent of its value. Then if it locates in column K anything less than 25,000 if it will highlight the entire row green. Lastly I want it to search column c and k and if it finds crl and less than 100,000 respectively for the entire row to be highlighted green.

Originally Posted by JBeaucaire

It appears the two loops at the end are evaluating the same range. Is the CRL the same first 3 letters?

If so, you can handle all three loops at once by changing the end to something like this:

Dim Data, del As Range
Dim Cll As Excel.Range
Dim m As Long
Dim n As Long

Range(Cells(1, 5), Cells(Rows.Count, 5).End(xlUp)).Select
Set Data = Excel.Selection
    For Each Cll In Data.Cells
      Select Case Left(Cll.Value, 3)
        Case "995"
            Cll.EntireRow.Cut
            ActiveSheet.Paste Sheets("CCY").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Case "9FF"
            Cll.EntireRow.Cut
            ActiveSheet.Paste Sheets("Futures").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Case "CRL"
            cell.EntireRow.Interior.ColorIndex = 4
      End Select
    Next Cll
  
End Sub

Well the CRL will be searched for once it ahs been placed in the CCY tab so is it ok to leave it in that loop?

Originally Posted by JBeaucaire

No, if the CRL thing is happening elsewhere, you'll have to take it out. But you can still leave the other two loops merged as shown.

Maybe something like this to color all the CRL cell/rows from column C all at once, see if you can get this to work:

Dim Data, del As Range
Dim Cll As Excel.Range
Dim m As Long
Dim n As Long

Range(Cells(1, 5), Cells(Rows.Count, 5).End(xlUp)).Select
Set Data = Excel.Selection
    For Each Cll In Data.Cells
      Select Case Left(Cll.Value, 3)
        Case "995"
            Cll.EntireRow.Cut
            ActiveSheet.Paste Sheets("CCY").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Case "9FF"
            Cll.EntireRow.Cut
            ActiveSheet.Paste Sheets("Futures").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
      End Select
    Next Cll

Sheets("CCY").Activate

Range("C1").AutoFilter Field:=1, Criteria:="*CRL*"
Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Interior.ColorIndex = 4
Range("C1").AutoFilter

It says that there is a compile error: Named argument not found - This appears and highlights what I have marked in Red. What should I do from here? Also how do I instead of highlighting CRL, Italicize CRL? Then after Italicizing I want it to search for all CRLs that are less than 100,000 in column K and by doing so highlight that row in green.

Originally Posted by JBeaucaire

That should be Criteria1:=, sorry, forgot the 1.

Well, I forgot about that column K stuff. We'll have to build a loop and check them all.

Dim Data As Range, cell As Range
Dim m As Long, n As Long

Set Data = Range(Cells(1, 5), Cells(Rows.Count, 5).End(xlUp))
    For Each cell In Data.Cells
      Select Case Left(cell.Value, 3)
        Case "995"
            cell.EntireRow.Cut
            ActiveSheet.Paste Sheets("CCY").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Case "9FF"
            cell.EntireRow.Cut
            ActiveSheet.Paste Sheets("Futures").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
      End Select
    Next Cll

Sheets("CCY").Activate
Set Data = Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))
    For Each cell In Data.Cells
        If Cells.Value Like "*CRL*" Then
            cell.Font.Italic = True
            If cell.Offset(0, 8) > -1 And cell.Offset(0, 8) <= 100000 Then _
                cell.EntireRow.Interior.ColorIndex = 4
        End If
    Next cell
  
End Sub

Ok, I ran it and it only highlighted the column headers rather than highlighting those rows where it found CRL. What should I tweak?

Thanks for all of your help thus far. I have been able to accomplish everyting minus highlighting the entire row green if the value in column k is less than or equal to 25000 but greater than 0. Do you know how I could do this in VBA or even using the conditional formatting option in excel 2007?

Originally Posted by JBeaucaire

The code I provided should highlight the rows green, it did in my tests. I don't have 2007 so you'll have to press F1 in Excel and read up on CF in Excel 2007.

It is telling me that: "Out of memory" and the it highlights: If Cells.Value Like "*CRL*" Then

What might be causing this? Also how would I go back into the first tab, position, and delete all the rows that have just been cut?

Originally Posted by JBeaucaire

Ouch, my bad. ONE little letter made the script run out of control. Take that "s" off:

If Cells.Value Like "*CRL*" Then

change that to

If Cell.Value Like "*CRL*" Then

Thanks, that was great.

Do you remember the sorting function that you did for me? Well now I am trying to do another one after adding more data. I tried to just reinsert your code further down the macro but when I do it (after I have changed the 995 and 9FF to AM and CL and also changed the end location to the correct tabs the "Select Case Left(cell.Value, 2) is highlighted and said it is a Type mismatch. What shoudl I do to change this so that it will cut adn paste? Also is there a way to include in this code a way to delete all blank rows so that those leftover will all move to the top? Thanks again for your help