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, in other words. Any good add-ins?

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

      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.

    • 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