A macro in Excel VBA to change the background color of cells that contain a formula.

This VBA macro scans a specified range within the active worksheet in Excel, in this case, cells A1 to C10. If a cell contains a formula, the macro changes the background color of that cell to yellow. This macro is particularly useful for efficiently managing large spreadsheets or for quickly identifying which cells contain formulas.

Used Technologies

• VBA (Visual Basic for Applications) is a programming language used for creating macros in Excel. Using this language, operations on specific cell ranges can be automated.

Features

• Selecting the Active Worksheet: The macro automatically selects the worksheet that the user is currently working on (the active worksheet).
• Scanning Cells Within a Specified Range: In the code, a range of A1:C10 is specified, and the macro examines each cell within this range one by one.
• Checking for the Presence of a Formula: The .HasFormula property is used to determine whether each cell contains a formula (equation).
• Changing the Background Color: If a cell contains a formula, its background color is changed to yellow, specified using RGB values.

Sauce Code

Sub ColorFunctionCells()
    Dim ws As Worksheet
    Dim cell As Range
    Dim targetRange As Range
    
    ' Select the active worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    ' Define the range to apply color to (in this case, A1:C10)
    Set targetRange = ws.Range("A1:C10")
    
    ' Loop through each cell in the range
    For Each cell In targetRange
        ' Check if the cell contains a formula
        If cell.HasFormula Then
            ' Set the background color to yellow (specified using RGB values)
            cell.Interior.Color = RGB(255, 255, 0)
        End If
    Next cell
End Sub

' MIT License
' Copyright (c) 2023 [ANJI]
'
' Permission is hereby granted, free of charge, to any person obtaining a copy
' of this software and associated documentation files (the "Software"), to deal
' in the Software without restriction, including without limitation the rights
' to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
' copies of the Software, and to permit persons to whom the Software is
' furnished to do so, subject to the following conditions:
'
' The above copyright notice and this permission notice shall be included in all
' copies or substantial portions of the Software.
'
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
' IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
' FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
' AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
' LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
' OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
' SOFTWARE.

How to Use

1. Open Excel: First, open the Excel worksheet where you want to apply the coloring.
2. Open VBA Editor: Press the Alt + F11 keys to open the VBA Editor.
3. Add a New Module: In the VBA Editor, go to the “Insert” menu and choose “Module” to add a new module.
4. Copy and Paste the Code: Copy and paste the provided code into the new module.
5. Specify the Range: In the code, modify the range in Set targetRange = ws.Range(“A1:C10”) to match the range you want to change.
6. Run the Macro: Either execute the macro (in this case, ColorFunctionCells) within the VBA Editor or go to the “View” tab in Excel and select the relevant macro from the “Macros” section to run it.
7. Save: Save the worksheet or workbook as needed.
8. That covers the basic usage. By following these steps, you can easily identify cells within a specific range that contain formulas.