Need to color five different cells to the right of a cell if a condition is met for a specific range of cells

excel conditional formatting based on another cell range
excel conditional formatting based on another cell multiple values
excel conditional formatting formula based on another cell
excel conditional formatting formula greater than and less than
excel formula color cell if value
conditional formatting if statement
excel conditional formatting based on another cell color
excel conditional formatting formula multiple conditions

I am new to VBA so any help is greatly appreciated. For a specific range of cells, I have if statements. So for A25:A31 if cells.value = 1 then I want cells (x,4) (x,7) (x,10) (x,13) in that same row that the condition is met to all have the same background color. I have tried to offset but it only works for one offset selection not multiple. Here is my code:

Set Rng1 = Range("A25:A31")
Set Rng2 = Range("B25:B31")
For Each cell In Rng1
    If cell.Value = "1" Then cell.Offset(0, 1).Cells.Interior.Color = Range("B12").Interior.Color
    If cell.Value = "2" Then cell.Offset(0, 1).Cells.Interior.Color = Range("B13").Interior.Color
    If cell.Value = "3" Then cell.Offset(0, 1).Cells.Interior.Color = Range("B14").Interior.Color
    If cell.Value = "4" Then cell.Offset(0, 1).Cells.Interior.Color = Range("B15").Interior.Color
    If cell.Value = "5" Then cell.Offset(0, 1).Cells.Interior.Color = Range("B16").Interior.Color
Next Cell

So I basically want something that will do the following:

If cell.Value = "1" Then cell.Offset(0, 1 & 4 & 7 & 10).Cells.Interior.Color = Range("B12").Interior.Color

so instead of changing only the offset one column over it also changes the cell to the right, and the cell four over, and the cell seven over etc..

Thanks for any help.

You could try this:

Option Explicit
Sub Test()

    Dim C As Range
    With ThisWorkbook.Sheets(1) 'change this for the name of your sheet
        For Each C In .Range("A25:A31")
            If C = 1 Then
                Union(C, .Cells(C.Row, 4), .Cells(C.Row, 7), .Cells(C.Row, 10), .Cells(C.Row, 13)).Interior.Color = 2 'whatever colour you need
            End If
        Next C
    End With

End Sub

How to use conditional formatting in Excel 2016, 2013 and 2010, You can apply conditional formatting to one or several cells, rows, columns it makes more sense to base your condition format on a certain cell's value. you do not want the other rules to be applied when the first condition is met. You can always select a range of cells in Excel using the standard range� Fill a cell with color based on a condition. Before learning to conditionally format cells with color, here is how you can add color to any cell in Excel. Cell static format for colors. You can change the color of cells by going into the formatting of the cell and then go into the Fill section and then select the intended color to fill the cell.

I agree with what @JohnColeman commented, but if you want vba, I think this is is what you are looking for. But @TimWilliams is more advanced.

Sub ColorCells()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
    For Each cel In ws.Range("A25:A31")
        If cel.Value = 1 Then
            cel.Resize(, 2).Interior.Color = Range("B12").Interior.Color
            cel.Offset(, 3).Interior.Color = Range("B12").Interior.Color
            cel.Offset(, 6).Interior.Color = Range("B12").Interior.Color
            cel.Offset(, 9).Interior.Color = Range("B12").Interior.Color

        ElseIf cel.Value = 2 Then
            cel.Resize(, 2).Interior.Color = Range("B13").Interior.Color
            cel.Offset(, 3).Interior.Color = Range("B13").Interior.Color
            cel.Offset(, 4).Interior.Color = Range("B13").Interior.Color
            cel.Offset(, 9).Interior.Color = Range("B13").Interior.Color

        ElseIf cel.Value = 3 Then
            cel.Resize(, 2).Interior.Color = Range("B14").Interior.Color
            cel.Offset(, 3).Interior.Color = Range("B14").Interior.Color
            cel.Offset(, 6).Interior.Color = Range("B14").Interior.Color
            cel.Offset(, 9).Interior.Color = Range("B14").Interior.Color

        ElseIf cel.Value = 4 Then
            cel.Resize(, 2).Interior.Color = Range("B15").Interior.Color
            cel.Offset(, 3).Interior.Color = Range("B15").Interior.Color
            cel.Offset(, 6).Interior.Color = Range("B15").Interior.Color
            cel.Offset(, 9).Interior.Color = Range("B15").Interior.Color

        ElseIf cel.Value = 5 Then
            cel.Resize(, 2).Interior.Color = Range("B16").Interior.Color
            cel.Offset(, 3).Interior.Color = Range("B16").Interior.Color
            cel.Offset(, 6).Interior.Color = Range("B16").Interior.Color
            cel.Offset(, 9).Interior.Color = Range("B16").Interior.Color
        End If
    Next cel
End Sub

Highlight patterns and trends with conditional formatting in Excel for , This changes the appearance of a cell range based on a condition (or criteria). formatting to highlight cells that contain values which meet a certain condition. And here's an example with 3-color scale conditional formatting applied: If you want to find only cells with a specific conditional format, start by clicking a cell� Here in B2 cell, we have the text which is displaying “Passed the Exam”. We will be targeting this cell to perform the conditional formatting for the entire range of cells. The procedure is given below. First, write down the text “Pass” in cell C2. Select the range of cells where you want to apply the conditional formatting.

Dim v 
Set Rng1 = Range("A25:A31")
Set Rng2 = Range("B25:B31")

For Each cell In Rng1.Cells
    v = cell.Value

    Select Case v
        Case 1,2,3,4,5: 
            cell.EntireRow.Range("D1,G1,J1,M1").Interior.Color = _
                  Range("B11").Offset(v, 0).Interior.Color
    End Select

Next Cell

Note the Range() is relative to EntireRow, so as written it will always operate on that row.

Conditional Formatting in Excel: The Ultimate Guide with Examples, Conditional formatting in Excel highlights cells that meet a specified condition. You can specify the format to be applied by using the right drop down. can be in a single column, multiple columns, or in a non-contiguous range of cells. I need cell D5 – D17 to be highlighted in different colors if a specific value comes in� When Cell A4 is being checked for the condition, it will check Cell E4. When Cell A5 is checked for the condition, it will check Cell E5 and so on. This process will highlight the entire rows having Pending status. Things to Remember. In this article, we have used text criteria for highlighting the row or cells based on another cell value.

Excel 2016: Conditional Formatting, Let's say you have a worksheet with thousands of rows of data. as colors, icons , and data bars—to one or more cells based on the cell value. For example, a conditional formatting rule might be: If the value is less than $2000, color the cell red. You can apply multiple conditional formatting rules to a cell range or� On the Ribbon's Home tab, click Conditional Formatting, then click New Rule. Click Use a Formula to Determine Which Cells to Format. For the formula, enter =AND ($B$2=$E$2,D5=$F$2) Click the Format button. Select red as the fill colour, and click OK. Click OK, to apply the conditional formatting.

MS Excel 2010: Automatically highlight highest and lowest values in , highlight the highest and lowest values in a range of cells in Excel 2010 (with is there a way to shade one cell green if it is the highest value in a range of cells, Next, we need to select what formatting to apply when this condition is met. In this example, the preview box shows green as the fill color. All rights reserved. I know how to change the color of an individual cell based on the entry (using the "Format only cells that contain" option), but this time I want to know how affect the color of the entire row based on the value of a cell within a row.

Excel Conditional Formatting How-To, If appropriate, you can use Excel formulas in the rules you write. Applying Multiple Conditions: You can apply multiple rules to a single cell or range of cells. Highlight rules apply color formatting to cells that meet specific criteria that you define To apply highlight rules, select the range of values you want to apply a rule to. Use Excel formula to change background color of special cells. A cell's color will change automatically based on the cell's value. This method provides a solution that you will most likely need in 99% of cases, i.e. the background color of cells will change according to the conditions you set.

  • Based on your problem description, you could use conditional formatting rather than VBA
  • Glad it help. Mark it as answer so others can find it.