Conditional Formatting Based on Cells Near 1 (Red) and 2 (Green) in Excel

When working with Excel spreadsheets, it is often useful to format cells based on their values or the values of nearby cells. In this article, we will cover how to conditionally format cells near a specific value, such as 1 or 2, and change their color to red or green, respectively.

Key Concepts

• Conditional Formatting
• Excel Formulas
• IF Statement
• NEARBY Function (not a built-in Excel function, but we will create a custom function)

Conditional Formatting

Conditional formatting is a feature in Excel that allows you to apply formatting, such as colors, fonts, and borders, to cells based on certain conditions. These conditions can be based on the value of a cell, a formula, or even the value of a nearby cell.

Excel Formulas

Excel formulas are used to perform calculations on data within a spreadsheet. They can be used to perform simple arithmetic operations, such as addition and subtraction, or more complex calculations, such as finding the average of a range of cells.

IF Statement

The IF statement is a conditional statement in Excel that allows you to perform an action based on whether a condition is true or false. The basic syntax of the IF statement is:

=IF(logical\_test, value\_if\_true, value\_if\_false)

NEARBY Function

The NEARBY function is not a built-in Excel function, but we will create a custom function to find cells near a specific value. The basic syntax of the NEARBY function is:

=NEARBY(value, range)

Where:

• value is the value for which we want to find nearby cells.
• range is the range of cells we want to search for nearby cells.

Subtitles

• Setting up the Spreadsheet
• Creating the NEARBY Function
• Applying Conditional Formatting
• Summary and References

Setting up the Spreadsheet

Before we can start applying conditional formatting, we need to set up our spreadsheet. In this article, we will use the following data:

| Column A | Column B | | --- | --- | | A1 | 3 | | A2 | 1 | | A3 | 2 | | A4 | 4 | | A5 | 1 | | A6 | 3 | | A7 | 2 | | A8 | 1 | | A9 | 4 | | A10 | 2 |

Creating the NEARBY Function

As mentioned earlier, the NEARBY function is not a built-in Excel function. However, we can create a custom function using the following code:

Function NEARBY(value As Variant, range As Range) As Range
    Dim cell As Range
    Dim nearbyCells As Range

    For Each cell In range
        If Abs(cell.Value - value) <= 1 Then
            If nearbyCells Is Nothing Then
                Set nearbyCells = cell
            Else
                Set nearbyCells = Union(nearbyCells, cell)
            End If
        End If
    Next cell

    Set NEARBY = nearbyCells
End Function

This function will search for cells in the specified range that are within one unit of the specified value. In our case, we will use this function to find cells near 1 and 2.

Applying Conditional Formatting

Now that we have our NEARBY function, we can apply conditional formatting to our spreadsheet. We will apply different formatting to cells near 1 and 2.

Cells Near 1

To format cells near 1, we will use the following steps:

1. Select the range of cells we want to format. In our case, we will select Column B.
2. Click on the "Conditional Formatting" button in the "Home" tab.
3. Select "New Rule".
4. Select "Use a formula to determine which cells to format".
5. Enter the following formula: =NEARBY(1, $B$1:$B$10)
6. Click on the "Format" button.
7. Select the "Fill" tab.
8. Choose a red color.
9. Click "OK" to apply the formatting.

Cells Near 2

To format cells near 2, we will use the following steps:

1. Select the range of cells we want to format. In our case, we will select Column B.
2. Click on the "Conditional Formatting" button in the "Home" tab.
3. Select "New Rule".
4. Select "Use a formula to determine which cells to format".
5. Enter the following formula: =NEARBY(2, $B$1:$B$10)
6. Click on the "Format" button.
7. Select the "Fill" tab.
8. Choose a green color.
9. Click "OK" to apply the formatting.

Summary and References

In this article, we covered how to conditionally format cells near specific values in Excel. We created a custom NEARBY function to find cells within one unit of a specified value. We then applied different formatting to cells near 1 and 2.

Types of References:

• Books:
  • "Excel Formulas and Functions for Dummies" by John Walkenbach
• Articles:
  • "How to Use Conditional Formatting in Excel" by Chris Dutton, Jumpstart: Spreadsheets
    • "Creating Custom Functions in Excel" by Microsoft Support
• Online Resources:
  • "NEARBY Function" by ExcelJet
    • "Conditional Formatting in Excel" by Microsoft Support