How do I fill the color of the cell below the active cell if the two values are the same?

Slack

I have a set of data with one column that contains two specific values that should alternate down each row.

The data looks like this:

Apparently I can't post images yet. The example is this:

C2: Break C3: Normal C4: Break C5: Normal C6: Normal C7: Break C8: Normal

Data

The goal is to fill Cell C6 Red.

The number of rows in the worksheet will change weekly, but the pattern "Break, Normal, Break" should remain consistent. When "Break, Normal, Normal" occurs, the last "Normal" needs to be filled to indicate that a break is missing.

My solution is to begin at C2, and if C3 is equal to C2, then fill C3 red. Then, step down a row and repeat the check until the end of the list. This logic would indicate that C6 turns red when C5 is Active.

I don't have a background in programming so I'm not sure if I'm approaching this the correct way, but here is the code I have:

Sub Compare_Rows()

   
    Range("C2").Select
     
    If (ActiveCell.Value = ActiveCell.Offset(1, 0).Value) Then
        ActiveCell.Offset(1, 0).Interior.ColorIndex = 3
    
    End If

        Do Until IsEmpty(ActiveCell)
        
        ActiveCell.Offset(1, 0).Select
    Loop
        

End Sub

In review I have added a break at the If and Loop lines, and the program does select each cell one row at a time. In this example when C5 is active, the tooltip for the two values in the If line confirm that the criteria (identical values) is met, but the target cell remains without any color.

The tooltip for the ColorIndex shows a color index of "-4142" at all times.

FaneDuru

Please, use the next code. It places the range in an array, for faster iteration/processing and build a Union range of duplicate cells value to be colored at the end of the code, at once:

   Dim sh As Worksheet, lastR As Long, rngCol As Range, arr, i As Long
   
   Set sh = ActiveSheet
   lastR = sh.Range("C" & sh.rows.count).End(xlUp).row
   
   arr = sh.Range("C1:C" & lastR).Value2
   
   For i = 2 To UBound(arr)
        If arr(i, 1) = arr(i - 1, 1) Then addToRange rngCol, sh.Range("C" & i)
   Next i
   If Not rngCol Is Nothing Then rngCol.Interior.Color = vbRed
End Sub

Sub addToRange(rngU As Range, rng As Range)
    If rngU Is Nothing Then
        Set rngU = rng
    Else
        Set rngU = Union(rngU, rng)
    End If
End Sub

Your code loops for nothing until it reaches the first empty cell...

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How do I align a UserForm next to the active cell?

How do I color a cell with openpyxl while retaining the grid lines?

How do I make a link fill a table cell of varying height?

Fill blank cells with cell values below

How to change value in dataframe cell if there are two different values with the same key

How do I get a function to return values in adjacent cells instead of being printed below the cell where the function is written?

How do I change the color of a cell under a conditional statement?

How do i Combine text of two cell values into one |EXCEL|VBA|

How can I fill an html table data cell with a color?

How do I fill down a column, copying the cell above, unless the adjacent cell has a value, then copy that value?

How to change value of a cell based on cell fill color

How do I make two items in the same table cell have different text alignments?

How do I fill a cell in the table dynamically?

Excel - How do I make the text in a cell flow over into the cell below

How do I make a div fill an entire table cell?

How to fill color in a cell in VBA?

VBA how do I edit cell contents based on contents of two other cells in same row

How do I fill down the rest of a column in the next available cell?

GridBagLayout - How can I make this cell fill the space below it?

In Excel, how do I conditionally format a cell after matching two distinct values across two separate worksheets?

How do I change the background color of a cell by a condition?

How to fill color in particular cell in dataframe?

How do I read a cell below a cell with a keyword in it

How do I create rows in a table based off a cell value and fill a column also based off the cell values in Excel

Fill cell color in if two percentage nearly same

how do i color a part of a cell in an html table?

How do I add values to the same cell with its current value?

Grafana: How to set Table cell color by comparing two values

How do I change the background color of a cell in Pandas?