| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| textBox font colour the same as cell font colour???????? | Excel Discussion (Misc queries) | |||
| How can colour of cell shading be fixed to one colour | Excel Discussion (Misc queries) | |||
| change a cell background colour to my own RGB colour requirements | Excel Discussion (Misc queries) | |||
| How can i change cell colour depending on month of date in cell? | Excel Discussion (Misc queries) | |||
| Conditional Format with VBA - Interior Colour of cell based on value from in-cell dropdown | Excel Programming | |||
![JJ[_8_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
