Excel Autofill by color

[Devon1990]

How do you autofill cells that have a certain color? I have thousands of red cells that need a formula in the cell boundaries and they are not directly above or below one another. They are scattered, in other words. Any good add-ins?

[#2]

@Devon1990 

Sub formula()

Dim cell, rng As Range

Set rng = Selection

For Each cell In rng

If cell.Interior.ColorIndex = 3 Then
cell.formula = "=today()"
Else
End If

Next cell

End Sub

Is this similar to what you are looking for? You can select a range with the mouse or with ctrl+G. Then you can run the macro and the code inserts the TODAY() formula in all red cells (Interior.ColorIndex = 3) within the selected range.

[#3]

That looks correct. I tried entering it in, but my dialog box is white and has no numbers (I selected the visual basic button). Am I in the wrong spot? I tried running the macro but nothing happened. Also, how would I modify this code to apply it to the light green color and the bright red color?

[#4]

The other thing is that I want it to do what happens when you drag to autofill. The cell you drag auto adjusts the formula reference location too. I don't know how else to explain it, so hopefully that helps.

[#5]

@Devon1990 

In the attached file you can select e.g. range B3:E25 with the mouse. Then you can run the macro in order to select cells with light green color and bright red color and insert a function with dynamic reference location in these cells.

Sub formula()
   Dim Cell, selectedrange As Range
   
   Set selectedrange = Selection
   
   For Each Cell In selectedrange
   If Cell.Interior.ColorIndex = 48 Or Cell.Interior.ColorIndex = 3 Then
    Cell.FormulaR1C1 = "=MATCH(RC1,RC6:RC10,0)"
   Else
   End If
   Next Cell
   
End Sub

The Interior.ColorIndex of the cells with light green color is 48 and the Interior.ColorIndex of the cells with bright red color is 3. You can use the user definded function GetInteriorColorIndex() in order to return the Interior.ColorIndex of a cell. Below is the code of GetInteriorColorIndex().

Function GetInteriorColorIndex(Cell As Range)

Dim StringColor As Integer
StringColor = Cell.Interior.ColorIndex
GetInteriorColorIndex = StringColor

End Function

In cell B2 of the attached file i've entered GetInteriorColorIndex() in order to return the Interior.ColorIndex of a cell with light green color.

The cells with light green color or bright red color are scattered in a small sample range in the attached file which could be similar to what you are looking for. Perhaps you can attach a sample file without sensitive data and share the formula which you want to insert in the red or green cells.

I think the same result can't be achieved by dragging a formula across a range because VBA is required to identify the background color of a cell and dragging a formula would enter the formula in cells with other background color as well.