value of any cell in row 1 = 8 then colour the whole row
 

How would I do this please?

If the value of any cell in row 1 = 8 then colour the whole row light
yellow.

value of any cell in row 1 = 8 then colour the whole row
 

Hi JJ,

If Evaluate("=COUNTIF(A1:IV1,8)") 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If


"JJ" wrote in message
...
How would I do this please?

If the value of any cell in row 1 = 8 then colour the whole row light
yellow.

value of any cell in row 1 = 8 then colour the whole row
 

Thanks William, that works beautifully.


"William Benson" wrote in message
...
Hi JJ,

If Evaluate("=COUNTIF(A1:IV1,8)") 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If


"JJ" wrote in message
...
How would I do this please?

If the value of any cell in row 1 = 8 then colour the whole row light
yellow.

value of any cell in row 1 = 8 then colour the whole row
 

Sure ... a bit shorter code and also easier to see how to use it in looping
by rows ...

If Evaluate("=COUNTIF(1:1,8)") 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If
End Sub

"William Benson" wrote in message
...
Hi JJ,

If Evaluate("=COUNTIF(A1:IV1,8)") 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If


"JJ" wrote in message
...
How would I do this please?

If the value of any cell in row 1 = 8 then colour the whole row light
yellow.

value of any cell in row 1 = 8 then colour the whole row
 

Just so you don't stunt your learning growth, using evaluate would be the
slow way to call countif. It can be called directly from VBA

If Application.WorksheetFunction.COUNTIF(rows(1),8) 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If

--
Regards,
Tom Ogilvy


"JJ" wrote in message
...
Thanks William, that works beautifully.


"William Benson" wrote in message
...
Hi JJ,

If Evaluate("=COUNTIF(A1:IV1,8)") 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If


"JJ" wrote in message
...
How would I do this please?

If the value of any cell in row 1 = 8 then colour the whole row light
yellow.

value of any cell in row 1 = 8 then colour the whole row
 

Tom -- you're right on top of things as always!!! In this case, I was not on
the left hand side of the learning curve, just lazy.


Not in defence of my laziness, but ...

1) why are not all WorksheetFunctions supported? .. a fact which often
discourages me from bothering with WorksheetFunction.

2) Is there a performance difference?

3) Are their losses from the WorksheetFunction collection (or whatever it
is) with new releases of Excel, or only new additions?

"Tom Ogilvy" wrote in message
...
Just so you don't stunt your learning growth, using evaluate would be the
slow way to call countif. It can be called directly from VBA

If Application.WorksheetFunction.COUNTIF(rows(1),8) 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If

--
Regards,
Tom Ogilvy


"JJ" wrote in message
...
Thanks William, that works beautifully.


"William Benson" wrote in message
...
Hi JJ,

If Evaluate("=COUNTIF(A1:IV1,8)") 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If


"JJ" wrote in message
...
How would I do this please?

If the value of any cell in row 1 = 8 then colour the whole row light
yellow.

value of any cell in row 1 = 8 then colour the whole row
 

the general rule is that functions duplicated by VBA are not available in
the WorksheetFunctions collection.

According to Microsoft, evaluate or square brackets is slower. In most
cases I have found this to be true. It makes sense, because the entire
string has to be passed to Excel from VBA for evaluation. In other words,
Excel has to parse the string and determine that countif is the function
being called and interpret the string address into a range argument. Using
Worksheetfunction I would assume vba can call that function directly and
pass the reference to the range.

As far as I know, there are no losses from the worksheetfunction collection
(for example, round continues to exist even though VBA now has a round -
although the algorithms are different.) But certainly wouldn't be the
authority on this.

--
Regards,
Tom Ogilvy

"William Benson" wrote in message
...
Tom -- you're right on top of things as always!!! In this case, I was not
on
the left hand side of the learning curve, just lazy.


Not in defence of my laziness, but ...

1) why are not all WorksheetFunctions supported? .. a fact which often
discourages me from bothering with WorksheetFunction.

2) Is there a performance difference?

3) Are their losses from the WorksheetFunction collection (or whatever it
is) with new releases of Excel, or only new additions?

"Tom Ogilvy" wrote in message
...
Just so you don't stunt your learning growth, using evaluate would be
the
slow way to call countif. It can be called directly from VBA

If Application.WorksheetFunction.COUNTIF(rows(1),8) 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If

--
Regards,
Tom Ogilvy


"JJ" wrote in message
...
Thanks William, that works beautifully.


"William Benson" wrote in message
...
Hi JJ,

If Evaluate("=COUNTIF(A1:IV1,8)") 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If


"JJ" wrote in message
...
How would I do this please?

If the value of any cell in row 1 = 8 then colour the whole row
light
yellow.

value of any cell in row 1 = 8 then colour the whole row
 

Thanks Tom!!!!!!!!!! Really appreciate the depth you provide.
"Tom Ogilvy" wrote in message
...
the general rule is that functions duplicated by VBA are not available in
the WorksheetFunctions collection.

According to Microsoft, evaluate or square brackets is slower. In most
cases I have found this to be true. It makes sense, because the entire
string has to be passed to Excel from VBA for evaluation. In other words,
Excel has to parse the string and determine that countif is the function
being called and interpret the string address into a range argument.
Using
Worksheetfunction I would assume vba can call that function directly and
pass the reference to the range.

As far as I know, there are no losses from the worksheetfunction
collection
(for example, round continues to exist even though VBA now has a round -
although the algorithms are different.) But certainly wouldn't be the
authority on this.

--
Regards,
Tom Ogilvy

"William Benson" wrote in message
...
Tom -- you're right on top of things as always!!! In this case, I was not
on
the left hand side of the learning curve, just lazy.


Not in defence of my laziness, but ...

1) why are not all WorksheetFunctions supported? .. a fact which often
discourages me from bothering with WorksheetFunction.

2) Is there a performance difference?

3) Are their losses from the WorksheetFunction collection (or whatever it
is) with new releases of Excel, or only new additions?

"Tom Ogilvy" wrote in message
...
Just so you don't stunt your learning growth, using evaluate would be
the
slow way to call countif. It can be called directly from VBA

If Application.WorksheetFunction.COUNTIF(rows(1),8) 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If

--
Regards,
Tom Ogilvy


"JJ" wrote in message
...
Thanks William, that works beautifully.


"William Benson" wrote in message
...
Hi JJ,

If Evaluate("=COUNTIF(A1:IV1,8)") 0 Then
'Yellow
Cells(1).EntireRow.Interior.ColorIndex = 6
Else
'None
Cells(1).EntireRow.Interior.ColorIndex = -4142
End If


"JJ" wrote in message
...
How would I do this please?

If the value of any cell in row 1 = 8 then colour the whole row
light
yellow.