Color cell vba

[JOEE1979]

I have a short code but I need to tailor it to my needs, for the most part I am successful.
1. Originally "i" was "For i = 1 To 3", I tried to change it but its not working (I have made that part in Red font to make it easier to see my mess up).
2. For the range, is there a way I can only gear it to a certain cell color (for example RGB 255,0,0)?
Thanks

Private Sub CommandButton1_click()
Dim mycell As Range
Dim myrange As Range

Set myrange = Worksheets("Sheet1").Range("D4:AD72")

For Each mycell In myrange
    [COLOR=rgb(184, 49, 47)]For i = Worksheets("Sheet1").Range("E5")[/COLOR]
    If mycell.Value = Application.WorksheetFunction.Small(myrange, i) Then
    mycell.Interior.Color = 6750054
Else
     cell.Interior.Color = 14929080 'BLUE
    End If
    Next
    
Next mycell


End Sub

 

[Micron]

I suggest you always a) declare your variables (use Option Explicit) and b) properly indent you code so that you can see when you have not properly terminated code blocks. A code block is everything that fits in between its starting and ending part ( such as For and Next; With and End With; Select Case and End Select and so on).
That should help a lot.

A For / Next loop is based on a count; e.g. from 1 To 25 or whatever, so i cannot be a range: i = Worksheets("Sheet1").Range("E5")

You have 2 ranges involved so I don't know which one #2 question applies to, nor do I get why you need .Small in the worksheet function line if you're only concerned about the cell colour. Maybe describe what you want in English instead of code - or perhaps better yet, post pics or paste sheet ranges with formatting, data and notes.

 

[JOEE1979]

I suggest you always a) declare your variables (use Option Explicit) and b) properly indent you code so that you can see when you have not properly terminated code blocks. A code block is everything that fits in between its starting and ending part ( such as For and Next; With and End With; Select Case and End Select and so on).
That should help a lot.

A For / Next loop is based on a count; e.g. from 1 To 25 or whatever, so i cannot be a range: i = Worksheets("Sheet1").Range("E5")

You have 2 ranges involved so I don't know which one #2 question applies to, nor do I get why you need .Small in the worksheet function line if you're only concerned about the cell colour. Maybe describe what you want in English instead of code - or perhaps better yet, post pics or paste sheet ranges with formatting, data and notes.

I have a bunch of colored cells in a spreadsheet and they change depending on time of the day. The colored cells are scattered within the range I gave earlier. When I put a number in "E5" (ie5), I want it to take the smallest 5 number in that color (red) and turn them green and leave the rest of them in red.

 

[Micron]

I want it to take the smallest 5 number

I don't get it. Which of these fives is smaller?

5 3 6 7 5 9 8 1 5

I suggest you create something in a sheet that shows what you want then copy and paste from there.