Searching for entry in a range of shaded cells in Excel

[gem28]

Hi!

I am trying to find a way to search for a single value (say number or strings) in a shaded range of cells. That, if it found it and I type another value in the same range on another cell, its going to prompt me that I am not allowed to type anything anymore.

Below is the set of codes I tried but I get endless loop. Pls. help. Thank you so much.

'searches for the beginning of the shaded cells
Do While ActiveCell.Interior.ColorIndex = 34
ActiveCell.Offset(-1, 0).Select
Loop

'searches for next shaded cells
If ActiveCell.Interior.ColorIndex = xlNone Then
ActiveCell.Offset(1, 0).Select
End If

If ActiveCell.Interior.ColorIndex = 34 Then
If ActiveCell.FormulaR1C1 <> &quot;&quot; Then
cval = ActiveCell.FormulaR1C1
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If

End If

 

[RobBroekhuis]

I'd reconsider having the formatting of the cell govern its behavior. It would be better to keep an up-to-date named range within which you can search. As for the second part of your question, it's not clear to me what you're trying to accomplish. Please elaborate, and show what you've done so far.
Rob

 

[SkipVought]

Lets say you already have &quot;sex&quot; in one cell in the shaded range.

Then you type &quot;sex&quot; in another cell in the shaded range.

Then you procedure will...

1. permit you to have only ONE cell containing &quot;sex&quot; or

2. permit you to have only TWO cells containing &quot;sex&quot;?

Testing for shaded cell could be done like this...

Sub atest()
    Dim r As Range
    For Each r In ActiveSheet.UsedRange
        With r
            If .Interior.ColorIndex = 34 Then
             'Houston, we have something shady!
        
            End If
        End With
    Next
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[gem28]

Thanks for the help.

Would you know how to get the cell address or cursor position in Excel VBA? 'been tryin' to use the Address or Target property but it still does'nt give the result I need. Help pls. Thanks.

 

[xlbo]

To get the cell that is currently selected use ACTIVECELL.address

Within the sheet_CHANGE event you can use TARGET.address to get the address of the cell that has been changed Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[gem28]

Thanks xlbo. I've tried this though but is TARGET a reserved word/variable in Excel VBA?

 

[xlbo]

yup - don't try and declare a variable called Target
You can use Target in worksheet CHANGE or SELECTION_CHNAGE events to refer to the cell that was changed or the cell that has been selected. Target is a range and therefore has all associated properties such as address / value etc Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde