change background of a range of cells based on value

[davefish]

I have a number of cells in a row range that I wish to change background colour if anything it typed into any cell in the range. if a number of text is type into Range A11:H11 I would like the background to change from red to None.

I have tried to record a formulae but cannot get this to work. Can anyone help?

Davefish

 

[SkipVought]

hi,

Did you consider using Excel native Conditional Formatting feature? Use the ISBLANK() function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davefish]

Hi Skip,

I tried using conditional formating but it does not seem to have the logic I'm looking for for the cells in question. Perhaps I didin't explain the problem fully? If I have five adjacent cells and I have any value in a single cell within the range, then the complete range needs to have the background colour removed. This is a prompt for the operator to ensure one of the cells has an entry.

I had tried the following, but to no avail:_

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If IsEmpty(Range("a1:H1")) Then
Range("A1:H1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
Else
Range("A1:H1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End Sub

 

[SkipVought]

Hmmmmmmm???

A:H is 8 columns. Where does 5 come in?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

As suggested by Skip, this can be done with Conditional Formatting using the formula

=AND(AND(ISBLANK($A$1),ISBLANK($B$1),ISBLANK($C$1),ISBLANK($D$1)),AND(ISBLANK($E$1),ISBLANK($F$1),ISBLANK($G$1),ISBLANK($H$1)))

I had to nest some AND() because it got a "too many arguments" error if I did it all in one big AND(), even though help for AND() says nothing about a limit to the number of arguments.

 

[SkipVought]

If Dave is saying that ANY cell in the row between A & H that has SOMETHING, CLEARS the entire range, a simple CF formula would be...
[tt]
=COUNTA($A1:$H1)>0
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

A simpler way to save the day.
COUNTA()
Hooray!

 

[davefish]

Hi Skip,

Although I can get the formulae to work in the spreadsheet I must be leaving something out in VB as the following keeps giving errors

If CountA("a1:H1") > 0 Then
Range("A1:H1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
Else
Range("A1:H1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End Sub
Can you point to my errors please?

 

[SkipVought]

Why are do doing ANYTHING on VBA to accomplish this? The whole thing about CF is NO VBA REQUIRED!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

so how's this macro going to work? when does it fire? how does it fire?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davefish]

Hi Skip,

You're right, I had VBA firmly embossed in my mind and the CountA threw me also. I used the CF in conjunction with the formulae and it worked just fine.

Thank you

 

[SkipVought]

BTW,

if you do use a worksheet formula in VBA, you can't mix metaphors so to speak.
[tt]
If WorksheetFormula.CountA(Range(A1:H1)) > 0 Then
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davefish]

Thanks for the heads up Skip