Nov 06 2023 03:45 PM
Nov 06 2023 03:45 PM
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?
Nov 07 2023 03:02 AM
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.
Nov 10 2023 03:39 PM
Nov 10 2023 03:42 PM - edited Nov 10 2023 03:42 PM
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.
Nov 11 2023 01:47 PM
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.