$$Excel-Macros$$ Conditional Formatting using VBA

[George Barrowcliff]

Excel 2007-

I have a spreadsheet that is updated frequently with new rows of data.

I have a conditional format that is set for all rows and each time I add a
new row I have to manually adjust the last row to encompass the newly added
one.

In the attached spreadsheet, new SKU data is added at the bottom of the
existing data.  Column B is conditionally formatted based on the average of
all elements in Column B starting at $B$3 and going through the last newly
added $B$[new row].  The format is over the same range.  i.e. $B$3 to
$B$[new row].

I would like to do this through VBA triggered with a command button.


Find the last cell and substitute it into the conditional format equation
and range using VBA is the question.


TIA

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


LastCell.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Conditional Formatting Excel 2007

[George Barrowcliff]

OK, please see attachment.

zp18

On Thu, Sep 8, 2011 at 2:22 PM, Venkat CV  wrote:

> Hi ,
>
> See Below Links...
> http://www.contextures.com/xlcondformat03.html\
>
> http://www.dummies.com/how-to/content/applying-conditional-formatting-in-excel-2007.html
>
> *Best Regards,*
> *Venkat *
> *Chennai*
> *My Linked in profile 
> *
> *
> *
>
> On Wed, Sep 7, 2011 at 1:30 PM, zp18  wrote:
>
>> A1=the name of an employee, defined name is "Suspect"
>> A2= an amount of money, defined as "MaxLoss"
>> B1:B8 is a column of money defined as "Shortages"
>> C1:G8 is a range of employee names defined as "Associates"
>>
>>
>> if the money contained in MaxLoss is less than the amount in column of
>> Shortages and the "Suspect"'s name is contained in the Associates
>> range then do something(bold, red, flashing etc)
>>
>>
>> The intent is to match an employees name to shortages using
>> conditional formatting
>>
>> James$57.20 Grace   Ronnie  MernalynIrene   0
>> -50  $4.91  Julie   Jocelyn Tiffany
>> RyanMark
>> $22.50 Grace   Ronnie  James   Irene   Mernalyn
>> $(0.78)Julie   Tiffany Ryan
>>  Mark0
>> $50.06 Grace   James   MernalynIrene   0
>> $2.65  Julie   Tiffany RyanMark0
>> $24.28 Grace   0  00
>> 0
>> $(373.20)  Julie   Jocelyn Ryan0
>> 0
>>
>> What should the statement be?  I cannot get the hang of logical
>> operators in conditional formatting, any help would be appreciated.
>>
>> TIA
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *
> *
> *
> *
>
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
George W

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


ConditionalFormatting.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Arithmetic with blank cells

[George Barrowcliff]

I like Dave's answer.  It seems to work the best.  Thanks.

On Sun, Aug 1, 2010 at 1:28 AM, Dave Bonallack wrote:

>  Hi,
> Try changing the formula in M3 to:
>
> IF(COUNT(M1,M2)>0,*SUM(M1:M2)*,"")
>
> I think *M1+M2* is the problem. The '+' operand can't seem to handle
> non-numeric inputs, whereas the SUM function has a built-in ability to
> ignore them.
> Regards - Dave.
>
> --
> Date: Sat, 31 Jul 2010 22:42:34 -0700
> Subject: Re: $$Excel-Macros$$ Arithmetic with blank cells
>
> From: irvinep...@gmail.com
> To: excel-macros@googlegroups.com
>
> OK, here is an example:  The whole idea is to prevent a data entry
> spreadsheet having a bunch of zeroes in all the totalizing columns.  Cells
> K1,L1 and K2,L2 have been "Clear Contents", Cell M3 the totalizer cell is
> blank.  Put a 1 in K1 and the #Value error appears in M3
>
>
>   Cell M1:   =IF(ISNUMBER(K1),K1*L1,"")Cell M2:
> =IF(ISNUMBER(K2),K2*L2,"")Cell M3
> =IF(COUNT(M1,M2)>0,M1+M2,"")
> Type(ing) all the cells shows all numeric except M2(2) and M3(16)
>
> Clearly it is the "" in M2 that is causing the value error and excel is ok
> with multiplying the blank value in L1 to the 1 in K1 even though the result
> is blank.
>
> This is really what I am trying to accomplish, make the blank worksheet
> look blank where arithmetic is taking place.  Thanks to a post, I found a
> new option in Excel Options-Advanced-Display Options for This Worksheet-Show
> a zero in cells that have a zero value
> I unchecked that guy and at least the spreadsheet does not show the zeroes
> but of course there are times when zero is a legitimate value to display.
>
> Thanks for all the interest and the answers.
> zp18
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>



-- 
George W

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

Re: $$Excel-Macros$$ Arithmetic with blank cells

[George Barrowcliff]

OK, here is an example:  The whole idea is to prevent a data entry
spreadsheet having a bunch of zeroes in all the totalizing columns.  Cells
K1,L1 and K2,L2 have been "Clear Contents", Cell M3 the totalizer cell is
blank.  Put a 1 in K1 and the #Value error appears in M3


Cell M1:   =IF(ISNUMBER(K1),K1*L1,"") Cell M2:
=IF(ISNUMBER(K2),K2*L2,"") Cell M3
=IF(COUNT(M1,M2)>0,M1+M2,"")
Type(ing) all the cells shows all numeric except M2(2) and M3(16)

Clearly it is the "" in M2 that is causing the value error and excel is ok
with multiplying the blank value in L1 to the 1 in K1 even though the result
is blank.

This is really what I am trying to accomplish, make the blank worksheet look
blank where arithmetic is taking place.  Thanks to a post, I found a new
option in Excel Options-Advanced-Display Options for This Worksheet-Show a
zero in cells that have a zero value
I unchecked that guy and at least the spreadsheet does not show the zeroes
but of course there are times when zero is a legitimate value to display.

Thanks for all the interest and the answers.
zp18

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe