For Loop to Change Cell Colors and Ignore Cells Based on Color?

[shaun27]

Hi everyone, hoping for your expertise. I am working with a set of data and trying to create a macro that, based on the cells that are selected, will return the highest value with the first button click, the second highest value with the second button click, etc.

I have put together the below, which is providing the highest number of the selected cells and inputting it in the correct cell. However, in order to minimize the amount of code I need to write, I want to create a For loop that, after finding the cell range of the first highest number, will copy the value to B40 and then change the interior of the cell range from the selected cells that was used red. That way, when I click on the button again, the macro will ignore the red cell and find the next largest value in the selection and record that in B41, and so on and so forth.

Would really appreciate your help!!!

Sub Decider()

Set rng = Selection
newCell = 0

For Each cell In rng
If cell.Value > newHighest And cell.Interior.Color <> RGB(255, 0, 0) Then
newHighest = cell.Value
End If
Next
Range("B40") = newHighest

End Sub

 

[NeonRedSharpie]

This....this is tricky. But not impossible. So long as you promise to leave the B40:Bxxx range alone...let's do this.

Sub Decider()    
    Dim Rng As Range
    Dim output As Range
    Dim nhRow As Long
    Dim nhCol As Long
    Dim newHighest As Long
    
    
    Set output = Range(Cells(40, 2), Cells(100, 2))
    Set Rng = Selection
    newHighest = 0
    
    For Each cell In Rng
        If cell.Value > newHighest And cell.Interior.Color <> RGB(255, 0, 0) Then
        
            newHighest = cell.Value
            nhRow = cell.Row
            nhCol = cell.Column
            
        End If
    Next


    Cells(nhRow, nhCol).Interior.Color = RGB(255, 0, 0)
    
    For Each cell In output
        If cell.Value = "" Then
            cell.Value = newHighest
            Exit For
        End If
    Next
End Sub

 

[shaun27]

Thanks, Neon! I'm trying to run it but am experiencing a run error related to the portion of the code in which we're trying to change the interior color of the selected cell: Method '_Default' of object 'Range' failed.


To be honest, I'm really stumped as to why that's being flagged as a bug. Did it work on your end?

 

[NeonRedSharpie]

It works fine for me when I run the test. Are you on a pc or a mac? What version of excel are you running?

 

[shaun27]

I'm running on a mac, Office 2011. Could that really be making a difference?

 

[NeonRedSharpie]

Try replacing:

[COLOR=#333333]Interior.Color = RGB(255, 0, 0)[/COLOR]

with

[COLOR=#333333][FONT=Trebuchet MS]Interior.ColorIndex = 3[/FONT][/COLOR]

I seem to remember macs and pcs handling color codes differently. Other than that, not sure.

 

[shaun27]

Interesting. So the color portion works now and correctly highlights the highest number cell. However, when I click on the button again, it does not change the color of the cell containing the second-highest figure, nor record the new value. Is there an issue using the double quotation marks (hopefully not another mac-specific problem)?

 

[NeonRedSharpie]

Interesting. So the color portion works now and correctly highlights the highest number cell. However, when I click on the button again, it does not change the color of the cell containing the second-highest figure, nor record the new value. Is there an issue using the double quotation marks (hopefully not another mac-specific problem)?

Sub Decider()    Dim Rng As Range
    Dim output As Range
    Dim nhRow As Long
    Dim nhCol As Long
    Dim newHighest As Long
    
    
    Set output = Range(Cells(40, 2), Cells(100, 2))
    Set Rng = Selection
    newHighest = 0
    
    For Each cell In Rng
        If cell.Value > newHighest And cell.Interior.ColorIndex <> 3 Then
        
            newHighest = cell.Value
            nhRow = cell.Row
            nhCol = cell.Column
            
        End If
    Next




    Cells(nhRow, nhCol).Interior.ColorIndex = 3
    
    For Each cell In output
        If cell.Value = "" Then
            cell.Value = newHighest
            Exit For
        End If
    Next
End Sub

The issue is I forgot to tell you to change the other portion of the code that references the color. Try this code.

 

[shaun27]

Ah, silly me, works like a charm. You're awesome, thank you so, so much for the help!!! Really appreciated!

 

[NeonRedSharpie]

Anytime, glad I could help.