conditional format rage based on entries within range

[Simon]

I have a spreadsheet in excel 2003 where a range of cells contain different
text.

If there is no text in any of the cells within the range I would like the
range conditionally formated (red), however if any or all of the cells within
the range contain text then no format should be applied;e.g.

If A1 to D1 are blank format cells red, when text is entered into B1 remove
formatting.

All help greatly recieved.
Simon.

 

[Mike H]

Simon,

Select A1 to d1 and then

format|Conditional formatting - Select 'formula is'
enter the formula =A1=""
Pick a colour
OK
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Don Guillett]

Instead of CF try this worksheet change event that will color if all TRULY
blank
Right click sheet tab>view code>insert this
'--------
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrange As Range
Set myrange = Range("b1:d4")
If Intersect(Target, myrange) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Len(Application.Trim(Target)) < 1 Then
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
If Application.CountA(myrange) = 0 Then
myrange.Interior.ColorIndex = 3
Else
myrange.Interior.ColorIndex = -4142
End If
End Sub
'=======

 

[Simon]

Hi Mike,
that works well in terms of the formating for individual cells, but what I'm
trying to do is remove the formatting from the entire cell range once one
(or) more cells is filled. (The text to be inserted will be fairly random, so
I can't use a validated list either.)
Simon

 

[Simon]

Hi Don,
Thanks.
I have no idea what the code means but it works.
The only time the cells don't revert back to red if the range is empty is
when two or more cells are selected at the same time and the data is deleted
en mass rather than one cell at a time, in this instance the now empty range
remains un formatted - certainly not the end of the world for me, as once the
data is entered there should be no need to delete it, but thtought you might
be interested. Also, as with all novices' I haven't given the complete
picture - there are other cell ranges in the spreadsheet that I would like
formatting to different colours depending on whether the range is blank or
not, I tryed to duplicate your formula below the origonal I had entered but
excel returned an error and (don't mean to be greedy), is there anything I
can add to your code to repeat the command down the columns of the data range
for a (monthly) list of varying length. Or should I stick to looking for a
conditional format solution?
Cheers.
Simon.

 

[Don Guillett]

Could be better but will do. Notice that I changed to do this if in that
range instead of leaving. So, add blocks as desired. Let me know if you need
custom help.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrange As Range
Set myrange = Range("b1:d4")
If Not Intersect(Target, myrange) Is Nothing Then
If Target.Count > 1 Then GoTo nocolor
If Len(Application.Trim(Target)) < 1 Then
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
If Application.CountA(myrange) = 0 Then
myrange.Interior.ColorIndex = 3
Else
myrange.Interior.ColorIndex = -4142
End If
Exit Sub
nocolor:
myrange.Interior.ColorIndex = 3
End If

End Sub

 

[Simon]

Thanks again Don.
I have had a practice with the code you posted, but I've realised that it's
not covering what I'm trying to achieve, and because I haven't the first idea
regarding code language I can't make any adjustments - I can't even make a
colour change as I don't know what colourindex relates to which colour.
You mentioned adding blocks, but I'm afraid I don't know what sections
constitute a block.
Also, perhaps more pertinant than me not understanding the code; the data
will be entered into the cells of a row, and there are likely to be 30 or 40
rows per sheet per week. Using the code, would I need to identify each row
individually within the code?
One last thing, when I enter the code and return to view the spreadsheet it
remains unformatted until I have entered and then deleted data in the range.
When I use this 'live' I will be opening the template spreadsheet (with the
code already in it) then pasting in data from a data registry. Having
practiced this, the spreadsheet remains unformatted unless I delete
something. Agian, having never used code before I don't know how to make the
formatting active from the outset (i.e, the range is red as soon as I open
the template, and remains red until / unless data is pasted in). I'm grateful
for the time you have spent on this Don, but am not sure that a code route is
for me, as I am so out of my depth I don't even know what questions to ask to
help myself, and I can't keep relying on guys like you to take the time to
reply to each little niggle that crops up as I go along. I was hoping that
there would be a quick CF formula that I could enter into a cell then drag
along the cells I want affected, followed by a double click on the bottom rt
corner to send the formula all the way down the columns - I may just be out
of luck!
Thanks again.
Simon.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.