Forum Discussion

Devon1990's avatar
Devon1990
Copper Contributor
Nov 06, 2023

Excel Autofill by color

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,...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Nov 07, 2023

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.

Devon1990's avatar
Devon1990
Copper Contributor
Nov 10, 2023
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?
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 11, 2023

    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.

Resources