Skip Cells based on colour of cell

[Cremorneguy]

Hi there,

I have a macro where it will skip cells that have diagonal lines in the cell. This works well because i use it for data entry and sometimes theres a specific cell i don't need to put data into, therefore i put the diagonal lines in the cell and it will automatically skip the cell when entering the data. The VBA code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Borders(xlDiagonalDown).LineStyle = xlContinuous Then
        ' Has Line, Select Next Cell
        ActiveCell.Offset(1, 0).Range("A1").Select
    End If
End Sub

Now i'm using conditional formatting to highlight these cells in a shade of grey but i can't conditionally format the cell to place the diagonal lines in. Is there a VBA code similar to above where i can have the cell skipped over based on the colour of the cell? The RGB colour of the grey i'm using is:
R: 128
G: 128
B: 128

Is this possible?

Thanks

[pareshj]

Hi,

I have made little changes in your code and hope this is what you want:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Interior.Color = RGB(128, 128, 128) Then
' Has Line, Select Next Cell
ActiveCell.Offset(1, 0).Range("A1").Select
End If

End Sub

Regards,
Paresh J

[Vikas_Gautam]

Try this..
I hope this will do...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Borders(xlDiagonalDown).LineStyle = xlContinuous OR _
   Target.Interior.Color = RGB(128, 128, 128) Then
' Has Line, Select Next Cell
ActiveCell.Offset(1, 0).Range("A1").Select
End If
End Sub

[HaHoBe]

Hi, Cremorneguy,

Your opening post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

[Cremorneguy]

Ok i figured out the code thing, hopefully this has adhered to the rules. Apologies.

[Cremorneguy]

Ok i figured out the code thing, hopefully this has adhered to the rules. Apologies.

[Cremorneguy]

Thanks Pareshj,

This has worked, however only if i select the cell and colour it manually.

I've currently got the cells that are meant to be grey autosumming from another sheet. I have a conditional format using a formula ='Start'!$A1="X", so anything on the other sheet that has the letter X in the cell will automatically change specific cells on this sheet to grey. I'm using the same colour grey as in the VBA but the VBA won't work with the conditional formatted cells. Is there any way we can get the VBA to work on the conditionally formatted cells?

Many thanks

[pareshj]

Hi,

OfCourse! It will work for conditional formatting too. But you need to check whether you are using correct event as in this case you are using worksheet_selectionchange.


Regards,
Paresh J

[Cremorneguy]

Hi Paresh,

I'm not sure how to work it. I've put your code in but it's not working on my excel. I have the conditional format set so the cell colour and text is RGB 128,128,128 but it still doesn't work. Could you please have a look at my attached file and check if i have it set up correctly? It's only set up on the sheet named "1". The conditional format is set to reference cells in column AM, the cells in column AM are actually equalling to cells from sheet named "Start". Not sure if this changes anything.

Many thanks for your help.