Chase cell color for every other variable

C

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
104
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet where i input PO information for multiple buildings. Some of these PO's have multiple line items which also get included in the sheet.

I am wanting to change the cell color of every other PO to help define each PO. Im not sure if conditional formatting would be best or a macro.

Something similar to below.
PO
12345678
12345678
12345679
12345680
12345681

I was thinking the following but need some guidance if I'm heading in the right direction before writing the code.

VBA Code: 
For each cell in A to last row
If cell value = offset down 1 value then
     if cell.interior color =  white then
          select range of cell and offset down 1 to column J and change interior color to white
     else if cell.interior color = gray then
           select range of cell and offset down 1 to column J and change interior color to gray
     end if
elseif cell value <> offset down 1 value then
    if cell.interior color =  white then
          select range of offset down 1 to column J and change interior color to gray
     else if cell.interior color = gray then
           select range of offset down 1 to column J and change interior color to white
     end if
end if
next c

If i have this correct this will check to see if the values are equal and if so it will then check to see the active cells interior color and change the offset cell and row to match

If the cells are not it will then determine the active cells color and change the offset to the opposite. to
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Here is where I am at and I am getting a missing object error.

VBA Code: 
Sub highlight_cell()
    Dim LR As Long
    Dim C As Range
    Dim RNG As Range
    Dim IC As Long

    '15 gray
    '2 white
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    IC = Interior.ColorIndex
    Set RNG = Range(C.Offset(0, 11), C.Offset(1, 11))
    
    For Each C In Range("A2:A" & LR)
        If C.Value = C.Offset(1, 0).Value Then
            If C.IC = 2 Then
                RNG.IC = 2
                ElseIf C.IC = 15 Then
                    RNG.IC = 15
            End If
        ElseIf C.Value <> C.Offset(1, 0).Value Then
            If C.IC = 2 Then
                RNG.IC = 15
                ElseIf C.IC = 15 Then
                    RNG.IC = 2
            End If
        End If
    Next C
End Sub
 
Upvote 0
This is not a valid line:
VBA Code: 
    IC = Interior.ColorIndex
You need a range object before "Interior" (otherwise, it is the interior of what, exactly?).

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Joe4 said:
This is not a valid line:
VBA Code: 
    IC = Interior.ColorIndex
You need a range object before "Interior" (otherwise, it is the interior of what, exactly?).

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...


Thanks for that info I removed the DIM statements as i clearly had the idea wrong.

Here is what I hae now.

VBA Code: 
Sub highlight_cell()
    Dim LR As Long
    Dim C As Range

    '15 gray
    '2 white
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    For Each C In Range("A2:A" & LR)
        If C.Value = C.Offset(1, 0).Value Then
            If C.IC = 2 Then
                Range(C.Offset(0, 0), C.Offset(1, 11)).Select
                Selection.Interior.ColorIndex = 2
                ElseIf C.IC = 15 Then
                    Range(C.Offset(0, 0), C.Offset(1, 11)).Select
                    Selection.Interior.ColorIndex = 15
            End If
        ElseIf C.Value <> C.Offset(1, 0).Value Then
            If C.IC = 2 Then
                Range(C.Offset(0, 0), C.Offset(1, 11)).Select
                Selection.Interior.ColorIndex = 15
                ElseIf C.IC = 15 Then
                   Range(C.Offset(0, 0), C.Offset(1, 11)).Select
                    Selection.Interior.ColorIndex = 2
            End If
        End If
    Next C
End Sub

And I am getting an error saying the object doesnt support the property or method

I ran a test with active cell
VBA Code: 
Sub test()
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 11)).Select
Selection.Interior.ColorIndex = 15
End Sub

And this indeed does what I am searching for.

Any thoughts or ideas when looking at my current commands?
 
Upvote 0
What exactly is "C.IC"?
Rich (BB code): 
If C.IC = 2 Then

Also, note that you usually do not need to select ranges in order to work with them, and doing so actually slows your code down.
Usually, if one line ends in "Select" and the next begins with "Selection", you can typically combine those lines together, i.e.
lines like this:
Rich (BB code): 
                Range(C.Offset(0, 0), C.Offset(1, 11)).Select
                Selection.Interior.ColorIndex = 2
can be combined like this:
Rich (BB code): 
                Range(C.Offset(0, 0), C.Offset(1, 11)).Interior.ColorIndex = 2

While the Macro Recorder is often handy for getting snippets of code, is often very literal (records every cell selection, scrolling, etc), and the code can usually be cleaned up a little to become shorter and more efficient.
 
Upvote 0
See if this is what you want:
VBA Code: 
Sub highlight_cell()
    Dim LR As Long
    Dim C As Range

    '15 gray
    '2 white
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    For Each C In Range("A2:A" & LR)
        If C.Value = C.Offset(1, 0).Value Then
            If C.Interior.ColorIndex = 2 Then
                Range(C.Offset(0, 0), C.Offset(1, 11)).Interior.ColorIndex = 2
            ElseIf C.Interior.ColorIndex = 15 Then
                    Range(C.Offset(0, 0), C.Offset(1, 11)).Interior.ColorIndex = 15
            End If
        ElseIf C.Value <> C.Offset(1, 0).Value Then
            If C.Interior.ColorIndex = 2 Then
                Range(C.Offset(0, 0), C.Offset(1, 11)).Interior.ColorIndex = 15
            ElseIf C.Interior.ColorIndex = 15 Then
                   Range(C.Offset(0, 0), C.Offset(1, 11)).Interior.ColorIndex = 2
            End If
        End If
    Next C
    
End Sub
 
Upvote 0
Joe4 said:
See if this is what you want:
VBA Code: 
Sub highlight_cell()
    Dim LR As Long
    Dim C As Range

    '15 gray
    '2 white
  
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    For Each C In Range("A2:A" & LR)
        If C.Value = C.Offset(1, 0).Value Then
            If C.Interior.ColorIndex = 2 Then
                Range(C.Offset(0, 0), C.Offset(1, 11)).Interior.ColorIndex = 2
            ElseIf C.Interior.ColorIndex = 15 Then
                    Range(C.Offset(0, 0), C.Offset(1, 11)).Interior.ColorIndex = 15
            End If
        ElseIf C.Value <> C.Offset(1, 0).Value Then
            If C.Interior.ColorIndex = 2 Then
                Range(C.Offset(0, 0), C.Offset(1, 11)).Interior.ColorIndex = 15
            ElseIf C.Interior.ColorIndex = 15 Then
                   Range(C.Offset(0, 0), C.Offset(1, 11)).Interior.ColorIndex = 2
            End If
        End If
    Next C
  
End Sub
Click to expand...


Thanks I seem to forgot i had IC as a DIM statement and didn't adjust.

This code worked on my test sheet to some degree. When i cleared the sheet out and tested it again it stopped working.

I'm going to copy everything over to a new worksheet and see if tat helps as maybe something funky is going on with my active sheet.

Funny thing is i had remove the .selection and .selection and was wondering why that wouldn't work but its because I overlooked a previous DIM statement.
 
Upvote 0
@Joe4

I ran the macro on a test worksheet with just some numbers in the A column. It did almost everything correct but left one duplicate out. I reset the worksheet and types in a different string of numbers and it didn't work.

Figured the worksheet might have been messed up so i started a new sheet and still nothing.

I opened a workbook and tried again and nothing.

I restarted my PC and tried the above again and nothing.

No error codes.

Any thoughts?
 
Upvote 0
crazyeyeschase said:
@Joe4

I ran the macro on a test worksheet with just some numbers in the A column. It did almost everything correct but left one duplicate out. I reset the worksheet and types in a different string of numbers and it didn't work.

Figured the worksheet might have been messed up so i started a new sheet and still nothing.

I opened a workbook and tried again and nothing.

I restarted my PC and tried the above again and nothing.

No error codes.

Any thoughts?
Click to expand...
Please show me an example where it is not working.
 
Upvote 0
Joe4 said:
Please show me an example where it is not working.
Click to expand...
If I run it thru VBA developer nothing happens, if i run it thru the macros popup it closes the popup but nothing on screen changes.

1701367474240.png

1701367532371.png




If I run a different macro for instance this highlight test command it works so I can confirm macros are enables and working

1701367913664.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Color Cell interiors in one sheet row range based on cells manually colored on another sheet.
2
Replies
13
Views
532
mumps
mumps
L
  • Solved
Insert formulas to new lines in table
Replies
3
Views
141
Ltuk85
L
O
  • Solved
ActiveX checkbox that changes color
2
Replies
15
Views
1K
John_w
J
S
  • Solved
Copy hyperlinks from one table column to another. The columns are identical
Replies
2
Views
395
sspatriots
S
N
  • Question Question
My code is suddenly really slow
Replies
1
Views
358
RobP
R

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top