graying out specific cells when data entered in another

[tjb]

When entering a specific number (or a list of numbers) in
a cell, I want another cell on the same sheet to gray
out. If the number is changed or removed I want the
other cells to go back to the way they were (not grayed
out). This code works with the exception of whenever
ANYTHING is changed on the sheet it goes back to
highlight Range("HOUR1, RATE1"). how do I get it to stop
doing that? Thanks! you all rock!

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("PDBA1").Value = "345" Then
Range("HOUR1, RATE1").Select
With Selection.Interior
.Pattern = xlGray25
End With
Range("ACCT_NUM1").Select
Exit Sub
Else
Range("HOUR1, RATE1").Select
With Selection.Interior
.Pattern = xlSolid
End With
Exit Sub
End If
End Sub

 

[Bernie Deitrick]

tjb,

Sounds like a job for conditional formatting, which would preclude your need
for code.

But back to your code. Simply don't use .Select. And only run the code when
cell PDBA1 changes...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("PDBA1").Address Then Exit Sub
If Target.Value = "345" Then
Range("HOUR1, RATE1").Interior.Pattern = xlGray25
Else
Range("HOUR1, RATE1").Interior.Pattern = xlSolid
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[tjb]

how would I use conditional formatting to format, for
instance, cell A1 if cell A2 changes? Doesn't
conditional formatting only format the cell that
changes? For instance, if cell A1 changes then cell A1
conditionally formats, not cell A2.

I may be understanding it wrong but clarity would be
appreciated!

 

[tjb]

I tried this out but when I enter "345" into PDBA1 t
doesn't gray HOUR1 and RATE1 until PDBA1 is selected
again. Likewise, it doesn't "ungray" HOUR1 and RATE1
after PDBA1 is changed or cleared until it is selected
again.

 

[Bernie Deitrick]

tjb,

You can do almost anything. In your case, you would select cell A2, the
select Format | Conditional Formatting... and instead of the default "Cell
Value is" select "Formula is" and use a formula that returns TRUE or FALSE.
In your case, use

=A1="345"
or
=A1=345

depending on whether your cell is a string or a number.

Then set the conditional format as usual.

HTH,
Bernie
MS Excel MVP

 

[tjb]

nevermind this question, I was using a selectionchange
instead of just change....oops!

 

[Guest]

so how would I do a range of numbers? like 255, 345, 261
and so on? can I reference a range of cells that I can
update at will?

 

[Bernie Deitrick]

Yes.

You could use the formula

=OR(A1=255,A1=345,A1=261)

or, for cell references

=OR(A1=B1,A1=C1,A1=D1)

Or, if you had a long list

=NOT(ISERROR(MATCH(A1,B1:Z1,FALSE)))

HTH,
Bernie
MS Excel MVP