VBA to insert .xlborder if cell value not equal to previous cell

I've got a worksheet and I'm wondering whether it is possible to
insert a line when a value in Column A, B, C & D does not equal the
values in the row above or below it.

I've currently got a formula in Column A that reads....
=IF(AND(B3=B2,C3=C2,D3=D2,E3=E2),"","IL") and a conditional format
that if the cell value is equal to "IL" then put a border.  Wondering
if there is a better way to do this via VBA or is that the better way?
0
Forgone
5/27/2010 2:33:04 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1665 Views

Similar Articles

[PageSpeed] 22

In your conditional formatting formula, instead of

=A1="IL"

(which I assume is what you've got)

Use:

=OR(B3<>B2,C3<>C2,D3<>D2,E3<>E2)



"Forgone" <stevenn@forgone.org> wrote in message 
news:4bdf2e9d-607a-4c4d-a08b-13fc63f59146@t26g2000prt.googlegroups.com...
> I've got a worksheet and I'm wondering whether it is possible to
> insert a line when a value in Column A, B, C & D does not equal the
> values in the row above or below it.
>
> I've currently got a formula in Column A that reads....
> =IF(AND(B3=B2,C3=C2,D3=D2,E3=E2),"","IL") and a conditional format
> that if the cell value is equal to "IL" then put a border.  Wondering
> if there is a better way to do this via VBA or is that the better way? 

0
Steve
5/27/2010 8:40:03 AM
Reply:

Similar Artilces:

How can I insert a cell reference in a footer (eg for variable foo #2
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = .Range("A1").Text End With End Sub This code should go in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "wngg001" <wngg001@discussions.microsoft.com> wrote in message news:8A0F9D9E-269F-45CF-A6E3...

VBA
I have created code that inserts lines into a financial statement but need to have certain cells in other code stay constant. B1 in certain code must stay at B1 regardless of changes to the spreadsheet. Any suggestions?? --- Message posted from http://www.ExcelForum.com/ Use the INDIRECT function. E.g., =INDIRECT("B1") The B1 will remain so regardless of inserting and deleting rows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "bforster1 >" <<bforster1.1cmxzp@excelforum-nospam.com> wrote in m...

excel VBA
How would i search a range of a whole column instead of specific cells like .Range("a1:a300")? --- Message posted from http://www.ExcelForum.com/ ..range("a:a") ..columns(1) ..range("a1").entirecolumn are a few ways. "Zygoid <" wrote: > > How would i search a range of a whole column instead of specific cells > like .Range("a1:a300")? > > --- > Message posted from http://www.ExcelForum.com/ -- Dave Peterson ec35720@msn.com ahhh! Thanks!! I was trying .Column(1) i see i neede an "s" in there Thanks a...

Macro Help/Duplicate Items + Insert Rows + Sum
I am trying to create a template that will do the following: 1. Find Duplicate Entries (AlphaNumeric) In A Column 2. Insert 2 Rows Between The Duplicate Entries Then: 1. Sub-Total(Another Column With Random Numbers) Of The Duplicate Entries 2. Format the Sub-Total In Bold I have gotten to the point of writting a macro that will identify the duplicate entries; does anybody know how to do the rest? This is a changing set of data, transferred to excel from a relational database (Lotus123 Rel2, which contains anywhere between 3000 to 5000 rows. I cannot spend time grouping the data ...

How to convert to absolute value?
Hello! How do I convert numbers to absolute value? Thanks Edit - Copy. Edit - PasteSpecial - Values - OK. Or do you want a code solution ? HTH. Best wishes Harald "Betsy Marlow" <cmarlow22@bellsouth.net> skrev i melding news:5WmHg.12873$j8.11911@bignews7.bellsouth.net... > Hello! > > How do I convert numbers to absolute value? > > Thanks > > Print abs(-12) 12 On Thu, 24 Aug 2006 15:16:31 -0400, "Betsy Marlow" <cmarlow22@bellsouth.net> wrote: >Hello! > >How do I convert numbers to absolute value? > >Thanks >...

Inserting specific number of records
I have a Data Entry form in which the user have to insert a specific numbrer of record, depending on the type of product he choose. Ej: The product "A" have to have 3 data, so it will need to insert 3 record on the table only, no more. I have created a simple code in VB that I supose to have to restrcit the number of record records, depending on a Textbox value **************************************** CODE **************************************** Private Sub LECTURA_BeforeUpdate(Cancel As Integer) On Error GoTo Err_Lectura Dim Respuesta If Form.CurrentRecord <= Forms...

Don't plot zero values
I am currently using Excel XP. I have a scatter chart and would only like to plot non zero values. These are not null values. Is there a chart setting I can do to skip plotting zero values or do I need to somehow filter/sort the data first and then plot? TIA George Hey George - If there are true zeros in the data, perhaps the easiest way to exclude them from the chart is to use an autofilter on the data that hides the rows with zeros. Alternatively, you could insert a column to hide the zeros. Say the range with zeros is in B1:B10. Select C1:C10, and enter this formula into C1: =IF...

diable command button based on field value
I have a command button which I would like to disable based on the following rules: If A = 1 then button is disabled If B = 2 then button is enabled Where you put the code depends on how the form works. If this needs to be checked for each record, use the form current event: If A = 1 Then Me.Button.Enabled = False ElseIf B = 2 Then Me.Button.Enabled = True End If But, what if A is not 1 and B is not 2? -- Dave Hargis, Microsoft Access MVP "barrynichols@gmail.com" wrote: > I have a command button which I would like to disable based on the >...

displaying negative values
how do I hide the display of negative numbers? =if(A1<0,"",A1) HTH Regards from Brazil Marcelo "cfuller" escreveu: > how do I hide the display of negative numbers? Hi, That will work if cell A1 has a formula in it. If negative numbers are just typed in, you can use conditional formatting. Highlight the range of cells you want to format. Go to Format > Conditional Formatting... Enter cell value is less than 0. Click the Format... button and select Color: and then click the white icon. HTH -- Ken Hudson "Marcelo" wrote: > =if(A1<0,"&qu...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

XY Chart
I have created a short VBA macro that reads x and y data from a spreadsheet, where every row represents a new series. Besides the x and y data colums, I also have columns that specify the size of the marker, the markerstyle, and the foreground and background colors. I have adapted some of John Walkenbach's code to label each of the datapoints using .name. The x axis is basically an integer 1 to 5 and the y axis is a value like price or variable margin. There usually are multiple datapoints on any given integer on the x axis. For example, I may be plotting the price that a custom...

Cell QA for Bob Phillips
Good afternoon Bob: In reference to your QA: (Which sheet was the active sheet when you right-clicked on the tab? It should have been Data Sheet). Yes, I did as you said, but I am not sure I placed the text string in the right place. I pasted it in View Code on the blank area to the right of "Project / Properties" section but for some reason it is not moving the text from "Data Sheet" to the "Activity Sheet". Is it possible to communicate with you directly so I can get this worked out? Thanks John ---------------------------------------------------...

parameter values not valid #3
keep getting a window each time I try and save a new task (Outlook 2000) it says: "Could not complete the operation. One or more parameter values are not valid" I just set up Outllook, and can't figure out why it would do this. ...

How to determine the closed value?
There is a number 14 in cell A1, and there is a list of number under column B, for example, 8, 11, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and 16 should be returned in cell A2. 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the larger number, and 16 should be returned in cell A2. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric Hi Eric, this should do it: =INDEX($B$1:$B$5,L...

Set value of const member variable in Init() function?
Is there a method for setting the value of a const member variable in an Init() function when the value is unknown in the constructor? TIA, Harvey "Harvey" <harveyab@juno.com> wrote in message news:1172562585.493563.230590@h3g2000cwc.googlegroups.com... > Is there a method for setting the value of a const member variable in > an Init() function when the value is unknown in the constructor? I don't believe so. But be careful of the difference between const int x=1; and const char *pszString; pszString = ... The latter is ok as the pointer is not const - th...

how do I get the if function to return a blank cell, not 0?
I am trying to create a chart from a series that contains data for each month. The series is calculated on other worksheets and copied to the worksheet containing the chart. I would like to have the cells for the months that have not been updated yet (now is January, there are 0' in all cells for Feb-Dec) to be blank (to create gaps in the chart) not 0's. Can this be done? Unfortunately, what you want, and what many of us have requested but doesn't exist, is a worksheet function like BLANK() or NULL(). The best we can do is use NA() in a chart's data source, which is...

How to save the value in the CEdit control to a text file?
Im generating a random number to the CEdit control and now i want t save all of this value to a text file. I dont know to code it in the way of MFC. I hope whose know it can send me the soding cause im running out the time to do my project thanks You can use GetWindowText to get its contents and then use CStdioFile class to write the text. MSDN has enough documentation on this. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "TLee" <anonymous@discussions.microsoft.com> wrote in message news:F730B5D6-85F7-45D9-80D9-C43598FA47B6@microsoft.com... | Im generating a random numbe...

Money 2007 delete previous versions?
I'd like to try Money 2007 trial before committing. I'm using Money 2004 SB. Does the upgrade wipe out any previous version so that I can't go back if I don't want to commit to the upgrade? I'm running XP SP2. Thanks, Brett It will give you the chance to select yes or no to remove it Steve -- Steve steven_morrison69@hotmail.com "brett" <account@cygen.com> wrote in message news:1163964503.767879.204210@m73g2000cwd.googlegroups.com... > I'd like to try Money 2007 trial before committing. I'm using Money > 2004 SB. Does the upgrade w...

How to slot cell values into pre-defined ranges
I would like to slot cell values starting from 500 upto 75000 in ranges (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have 15 bins. pls help Not sure what you want here. Do you want to count the number of times values within a range occur? -- Ian -- "KDD" <KDD@discussions.microsoft.com> wrote in message news:766017CE-A55E-42FF-AD8D-9E74E48CD568@microsoft.com... >I would like to slot cell values starting from 500 upto 75000 in ranges > (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have > 15 > bins. > > pls...

Formula help error of #VALUE!
I am not sure I can explain this clearly but I will give it a shot The cells are formatted to show time Cell A4 you enter in 1 or 2 to have cell A3 show the info from cell A2 (if 1) or B2 (if2) Formula for Cell A3 is =(COUNTIF(A4,"=1")*A2)+(COUNTIF(A4,"=2")*B2) It goes up to COUNTIF(A4,"=8")*H2) Problem is this is a time sheet so cells are formatted for time like 8:00:00 AM. Cell B2 I need to be text (Vacation) so staff will get paid vacation day and not show blank like a day off. When I enter text in cell B2 cellA3 shows #VALUE! How can I set this up so one c...

Inserting a Blank Line
I did ask for similar a little while back, but cannot somehow digest the code. What is the best way to insert a blank line where the data changes? The data is in column B. e.g. 14:45:00 14:45:00 14:45:00 14:45:00 15:00:00 15:00:00 15:00:00 15:00:00 15:00:00 15:15:00 15:15:00 15:15:00 15:15:00 15:15:00 to.. 14:45:00 14:45:00 14:45:00 14:45:00 15:00:00 15:00:00 15:00:00 15:00:00 15:00:00 15:15:00 15:15:00 15:15:00 15:15:00 15:15:00 Don't know if this is best, but certainly an easy method. Sub InsertRow_At_Change() 'Sandy Mann July 1st, 2007 Dim LastRow As Long Dim X A...

How to change default cell formats
When I open a file, all negative values are shown with a - before them, whilst my colleagues, when opening the same file, the negatives are shown in brackets ie. ( ). How do I ensure that when I open the same file, my negative numbers appear in brackets This is a windows setting. Close excel Change that windows regional setting|currency tab|Negative currency format to show ($1.1) Open excel You may have to reformat: Format|Cells|number tab|Number (adjust the decimal places and comma options) Easty04 wrote: > > When I open a file, all negative values are shown with a - before t...

look up one value that can list multiple corresponding values
I am trying to automate work timesheets by using a spreadsheet that references two different spreadsheets that list job numbers, names and task numbers. I currently have the spreadsheet set up to import and update the two spreadsheets containing job info. I also have the time sheet set up so that when a user types in the job number, the job name automatically appears in the proper location. I need to some how get the spreadsheet to look up the entered job number and return a dropdown list of available task numbers related to that specific job. Task numbers and quantities of task ...

graph creation from table using cells with links not possible
Hello, I have problem with MS Excel 2003. It´s impossible to create a graph from the table which is automatically fullfilled by the links to another sheet. I have to fullfill the table manually (numbers not links to them are physically in the cells) and only then I´m able to create the graph correctly. Is there some possibillity, how to create a graph from the table wich has links instead real numbers in the cells? Thanks for Your ideas. Regards, Bobr. ...

to find the row of the maximum value
Hi, I want a formula to find the row of the maximum value please help me out a 1 8 15 b 2 9 16 c 3 10 17 d 4 11 18 e 5 12 19 f 6 13 20 g 7 14 21 for this the result should be 7th row , 14th row, 21st row etc Thanks Sam =max(firstcol&row:lastcol&row) ie =max(a1:a100) Glen <sunil.s@ajubanet.net> wrote in message news:1143031777.439845.259520@t31g2000cwb.googlegroups.com... > Hi, > > I want a formula to find the row of the maximum value > > please help me out > > a 1 8 15 > b 2 9 16 > c 3 10 17 > d 4 11 18 > e 5 12 19 > f 6 13 20 > g ...