Compare and Highlight Rows

I have an excel file with two worksheets.  is there a way to programatically 
compare the two worksheets to find matches and highlight those matches on 
each worksheet.

They both have the same columns.  
I want to compare the InspectionID column.  
The Inspection ID column may have duplicates in both of the worksheets.

I have never done anything in excel above the beginner level.  However, I am 
a very skilled VBA programmer (programmed in ACCESS for over 10 years).

Thank you
0
LisaB (24)
9/11/2007 3:12:03 PM
excel.newusers 15348 articles. 2 followers. Follow

12 Replies
1138 Views

Similar Articles

[PageSpeed] 3

You may find it easier to use Conditional formatting instead:

For instance, with Sheet1 column A selected, and cell A1 active, 
checking against column B in sheet2:

Format/Conditional Formatting...

CF1:        Formula Is      =COUNTIF(Sheet2!B:B,A1) > 0
Format1:    <pattern>/<color>

Do something similar with CF in Sheet2, column B.

In article <73315C17-795F-4134-985A-E7BAD145B294@microsoft.com>,
 Lisab <Lisab@discussions.microsoft.com> wrote:

> I have an excel file with two worksheets.  is there a way to programatically 
> compare the two worksheets to find matches and highlight those matches on 
> each worksheet.
> 
> They both have the same columns.  
> I want to compare the InspectionID column.  
> The Inspection ID column may have duplicates in both of the worksheets.
> 
> I have never done anything in excel above the beginner level.  However, I am 
> a very skilled VBA programmer (programmed in ACCESS for over 10 years).
> 
> Thank you
0
jemcgimpsey (6723)
9/11/2007 3:23:42 PM
Not sure this works. CF does not allow reference to other worksheets
You could put the formula in a cell on Sheet 1 and use CF to look at its 
value.
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message 
news:jemcgimpsey-6CAC8E.09234211092007@msnews.microsoft.com...
> You may find it easier to use Conditional formatting instead:
>
> For instance, with Sheet1 column A selected, and cell A1 active,
> checking against column B in sheet2:
>
> Format/Conditional Formatting...
>
> CF1:        Formula Is      =COUNTIF(Sheet2!B:B,A1) > 0
> Format1:    <pattern>/<color>
>
> Do something similar with CF in Sheet2, column B.
>
> In article <73315C17-795F-4134-985A-E7BAD145B294@microsoft.com>,
> Lisab <Lisab@discussions.microsoft.com> wrote:
>
>> I have an excel file with two worksheets.  is there a way to 
>> programatically
>> compare the two worksheets to find matches and highlight those matches on
>> each worksheet.
>>
>> They both have the same columns.
>> I want to compare the InspectionID column.
>> The Inspection ID column may have duplicates in both of the worksheets.
>>
>> I have never done anything in excel above the beginner level.  However, I 
>> am
>> a very skilled VBA programmer (programmed in ACCESS for over 10 years).
>>
>> Thank you 


0
bliengme5824 (3040)
9/11/2007 3:38:12 PM
What do you think about this code.  I think this may work if I knew the code 
for highlighting a row

Sub Find_MatchesINZips()

Dim compareRange As Variant
Dim x As Variant, y As Variant

Set compareRange = Worksheets("Sheet2").Range("A2:A149")

For Each x In Selection
    For Each y In compareRange
        If x = y Then Highlighte Row ***(Need code here)***
    Next y
Next x

End Sub


"Bernard Liengme" wrote:

> Not sure this works. CF does not allow reference to other worksheets
> You could put the formula in a cell on Sheet 1 and use CF to look at its 
> value.
> best wishes
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> www.stfx.ca/people/bliengme
> remove caps from email
> 
> "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message 
> news:jemcgimpsey-6CAC8E.09234211092007@msnews.microsoft.com...
> > You may find it easier to use Conditional formatting instead:
> >
> > For instance, with Sheet1 column A selected, and cell A1 active,
> > checking against column B in sheet2:
> >
> > Format/Conditional Formatting...
> >
> > CF1:        Formula Is      =COUNTIF(Sheet2!B:B,A1) > 0
> > Format1:    <pattern>/<color>
> >
> > Do something similar with CF in Sheet2, column B.
> >
> > In article <73315C17-795F-4134-985A-E7BAD145B294@microsoft.com>,
> > Lisab <Lisab@discussions.microsoft.com> wrote:
> >
> >> I have an excel file with two worksheets.  is there a way to 
> >> programatically
> >> compare the two worksheets to find matches and highlight those matches on
> >> each worksheet.
> >>
> >> They both have the same columns.
> >> I want to compare the InspectionID column.
> >> The Inspection ID column may have duplicates in both of the worksheets.
> >>
> >> I have never done anything in excel above the beginner level.  However, I 
> >> am
> >> a very skilled VBA programmer (programmed in ACCESS for over 10 years).
> >>
> >> Thank you 
> 
> 
> 
0
LisaB (24)
9/11/2007 3:48:02 PM
Actually, I'm positive it doesn't, at least the way I posted. I tried to 
simplify, and forgot about the reference. Thanks for the correction, 
Bernard!


what DOES work is to NAME the columns in each sheet. For instance, name 
Column B in sheet2, say, "ID2"

Then in Sheet1, column A, use

CF1:        Formula is   =COUNTIF(ID2, A1)
Format1:    <pattern>/<red>




In article <#sDpGnI9HHA.980@TK2MSFTNGP06.phx.gbl>,
 "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote:

> Not sure this works. CF does not allow reference to other worksheets
> You could put the formula in a cell on Sheet 1 and use CF to look at its 
> value.
> best wishes
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> www.stfx.ca/people/bliengme
> remove caps from email
> 
> "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message 
> news:jemcgimpsey-6CAC8E.09234211092007@msnews.microsoft.com...
> > You may find it easier to use Conditional formatting instead:
> >
> > For instance, with Sheet1 column A selected, and cell A1 active,
> > checking against column B in sheet2:
> >
> > Format/Conditional Formatting...
> >
> > CF1:        Formula Is      =COUNTIF(Sheet2!B:B,A1) > 0
> > Format1:    <pattern>/<color>
> >
> > Do something similar with CF in Sheet2, column B.
0
jemcgimpsey (6723)
9/11/2007 3:51:54 PM
Just a warning about the name ID2--it looks way too much like a cell address
(when in A1 reference style).  

How about _ID2
(with a leading underscore)

JE McGimpsey wrote:
> 
> Actually, I'm positive it doesn't, at least the way I posted. I tried to
> simplify, and forgot about the reference. Thanks for the correction,
> Bernard!
> 
> what DOES work is to NAME the columns in each sheet. For instance, name
> Column B in sheet2, say, "ID2"
> 
> Then in Sheet1, column A, use
> 
> CF1:        Formula is   =COUNTIF(ID2, A1)
> Format1:    <pattern>/<red>
> 
> In article <#sDpGnI9HHA.980@TK2MSFTNGP06.phx.gbl>,
>  "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote:
> 
> > Not sure this works. CF does not allow reference to other worksheets
> > You could put the formula in a cell on Sheet 1 and use CF to look at its
> > value.
> > best wishes
> > --
> > Bernard V Liengme
> > Microsoft Excel MVP
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
> > news:jemcgimpsey-6CAC8E.09234211092007@msnews.microsoft.com...
> > > You may find it easier to use Conditional formatting instead:
> > >
> > > For instance, with Sheet1 column A selected, and cell A1 active,
> > > checking against column B in sheet2:
> > >
> > > Format/Conditional Formatting...
> > >
> > > CF1:        Formula Is      =COUNTIF(Sheet2!B:B,A1) > 0
> > > Format1:    <pattern>/<color>
> > >
> > > Do something similar with CF in Sheet2, column B.

-- 

Dave Peterson
0
petersod (12004)
9/11/2007 4:06:58 PM
OK, I'm officially too distracted to be posting here. I'm going back to 
just working...


Thanks for the correction, Dave.

In article <46E6BD22.7948F396@verizonXSPAM.net>,
 Dave Peterson <petersod@verizonXSPAM.net> wrote:

> Just a warning about the name ID2--it looks way too much like a cell address
> (when in A1 reference style).  
> 
> How about _ID2
> (with a leading underscore)
0
jemcgimpsey (6723)
9/11/2007 4:13:35 PM
Please, I am so close. I know this would work if I knew the syntax for 
accessing the current row and highlighting it.

Sub Find_MatchesINZips()

Dim compareRange As Variant
Dim x As Variant, y As Variant

Set compareRange = Worksheets(2).Range("A2:A149")

For Each x In Selection
    For Each y In compareRange
        If x = y Then x.Rows.BackColor = vbYellow   ****x.rows is not 
correct****
    Next y
Next x

End Sub



"JE McGimpsey" wrote:

> OK, I'm officially too distracted to be posting here. I'm going back to 
> just working...
> 
> 
> Thanks for the correction, Dave.
> 
> In article <46E6BD22.7948F396@verizonXSPAM.net>,
>  Dave Peterson <petersod@verizonXSPAM.net> wrote:
> 
> > Just a warning about the name ID2--it looks way too much like a cell address
> > (when in A1 reference style).  
> > 
> > How about _ID2
> > (with a leading underscore)
> 
0
LisaB (24)
9/11/2007 4:30:00 PM
PLEASE HELP - Using the following code I am getting the following error

Unable to set the pattern property of the interior class

Sub Find_MatchesINZips()

Dim compareRange As Variant
Dim x As Variant, y As Variant
Dim counter As Integer

Set compareRange = Worksheets(2).Range("A2:A149")
counter = 1

For Each x In Selection
    For Each y In compareRange
        If x = y Then Selection.Rows(counter).Interior.Pattern = vbYellow
    Next y
    counter = counter + 1
Next x

End Sub

"Lisab" wrote:

> I have an excel file with two worksheets.  is there a way to programatically 
> compare the two worksheets to find matches and highlight those matches on 
> each worksheet.
> 
> They both have the same columns.  
> I want to compare the InspectionID column.  
> The Inspection ID column may have duplicates in both of the worksheets.
> 
> I have never done anything in excel above the beginner level.  However, I am 
> a very skilled VBA programmer (programmed in ACCESS for over 10 years).
> 
> Thank you
0
LisaB (24)
9/11/2007 5:04:02 PM
Why swim the river to get to the water, you have been given a solution using 
conditional formatting?


-- 
Regards,

Peo Sjoblom





"Lisab" <Lisab@discussions.microsoft.com> wrote in message 
news:9CA10B48-85AA-4F21-9714-9D1319025B41@microsoft.com...
> Please, I am so close. I know this would work if I knew the syntax for
> accessing the current row and highlighting it.
>
> Sub Find_MatchesINZips()
>
> Dim compareRange As Variant
> Dim x As Variant, y As Variant
>
> Set compareRange = Worksheets(2).Range("A2:A149")
>
> For Each x In Selection
>    For Each y In compareRange
>        If x = y Then x.Rows.BackColor = vbYellow   ****x.rows is not
> correct****
>    Next y
> Next x
>
> End Sub
>
>
>
> "JE McGimpsey" wrote:
>
>> OK, I'm officially too distracted to be posting here. I'm going back to
>> just working...
>>
>>
>> Thanks for the correction, Dave.
>>
>> In article <46E6BD22.7948F396@verizonXSPAM.net>,
>>  Dave Peterson <petersod@verizonXSPAM.net> wrote:
>>
>> > Just a warning about the name ID2--it looks way too much like a cell 
>> > address
>> > (when in A1 reference style).
>> >
>> > How about _ID2
>> > (with a leading underscore)
>> 


0
terre081 (3244)
9/11/2007 5:05:21 PM
Because I know there is always more then one way to skin a cat.  That is what 
makes me a great programmer.  By the way, here is the solution.
------------------------------------
Dim compareRange As Variant
Dim x As Variant, y As Variant
Dim counter As Integer

Set compareRange = Worksheets(2).Range("A2:A149")
counter = 1

For Each x In Selection
    For Each y In compareRange
        If x = y Then Selection.Rows(counter).EntireRow.Interior.ColorIndex 
= 6
    Next y
    counter = counter + 1
Next x


"Peo Sjoblom" wrote:

> Why swim the river to get to the water, you have been given a solution using 
> conditional formatting?
> 
> 
> -- 
> Regards,
> 
> Peo Sjoblom
> 
> 
> 
> 
> 
> "Lisab" <Lisab@discussions.microsoft.com> wrote in message 
> news:9CA10B48-85AA-4F21-9714-9D1319025B41@microsoft.com...
> > Please, I am so close. I know this would work if I knew the syntax for
> > accessing the current row and highlighting it.
> >
> > Sub Find_MatchesINZips()
> >
> > Dim compareRange As Variant
> > Dim x As Variant, y As Variant
> >
> > Set compareRange = Worksheets(2).Range("A2:A149")
> >
> > For Each x In Selection
> >    For Each y In compareRange
> >        If x = y Then x.Rows.BackColor = vbYellow   ****x.rows is not
> > correct****
> >    Next y
> > Next x
> >
> > End Sub
> >
> >
> >
> > "JE McGimpsey" wrote:
> >
> >> OK, I'm officially too distracted to be posting here. I'm going back to
> >> just working...
> >>
> >>
> >> Thanks for the correction, Dave.
> >>
> >> In article <46E6BD22.7948F396@verizonXSPAM.net>,
> >>  Dave Peterson <petersod@verizonXSPAM.net> wrote:
> >>
> >> > Just a warning about the name ID2--it looks way too much like a cell 
> >> > address
> >> > (when in A1 reference style).
> >> >
> >> > How about _ID2
> >> > (with a leading underscore)
> >> 
> 
> 
> 
0
LisaB (24)
9/11/2007 6:00:01 PM
Except that your solution is less efficient, non-automatic, and will not 
work for users who disable macros.

Given your original problem statement, it seems to me a real 
disadvantage that you rely on the inspectionID column on Worksheets(1) 
being selected rather than specifying it in your macro.

Your macro also doesn't meet your criterion of "highlight(ing) those 
matches on each worksheet", as it will only highlight the rows on sheet 
1.

As a great programmer, you might also recognize that using variants is 
rather inefficient compared to using range objects or simple data types. 
And one should generally avoid using the Integer data type for rows, 
since Integers are limited to +32,767 and the number of rows isn't.

And, finally, the VBA comparison is likely much less efficient than 
using a built-in function, say:

    Const idCol1 As Long = 1  'worksheet 1 ID column
    Const idCol2 As Long = 2  'worksheet 2 ID column
    Dim compareRange as Range
    Dim rCell As Range

    With Worksheets(2)
        Set compareRange = .Range(.Cells(2, idCol2), _
            .Cells(.Rows.Count, idCol2).End(xlUp).Row)
    End With
    With  Worksheets(1)
        For Each rCell in .Range(.Cells(2, idCol1), _
                .Cells(.Rows.Count, idCol1).End(xlUp).Row)
           With rCell
                If Application.WorksheetFunction.CountIf( _
                    compareRange, .Value) > 0 Then _
                        .EntireRow.Interior.ColorIndex = 6
            End With
        Next rCell
    End With

In article <A7620721-623F-47A1-B5FB-D5E88BBC54ED@microsoft.com>,
 Lisab <Lisab@discussions.microsoft.com> wrote:

> Because I know there is always more then one way to skin a cat.  That is what 
> makes me a great programmer.  By the way, here is the solution.
> ------------------------------------
> Dim compareRange As Variant
> Dim x As Variant, y As Variant
> Dim counter As Integer
> 
> Set compareRange = Worksheets(2).Range("A2:A149")
> counter = 1
> 
> For Each x In Selection
>     For Each y In compareRange
>         If x = y Then Selection.Rows(counter).EntireRow.Interior.ColorIndex 
> = 6
>     Next y
>     counter = counter + 1
> Next x
> 
> 
> "Peo Sjoblom" wrote:
> 
> > Why swim the river to get to the water, you have been given a solution 
> > using 
> > conditional formatting?
0
jemcgimpsey (6723)
9/11/2007 7:38:31 PM
Hello!
please, try one useful add-in that i usually use for comparing spreadsheets:
http://www.office-excel.com/excel-addins/compare-spreadsheets.html

Regards,
Eugene
"Lisab" <Lisab@discussions.microsoft.com> wrote in message 
news:73315C17-795F-4134-985A-E7BAD145B294@microsoft.com...
>I have an excel file with two worksheets.  is there a way to 
>programatically
> compare the two worksheets to find matches and highlight those matches on
> each worksheet.
>
> They both have the same columns.
> I want to compare the InspectionID column.
> The Inspection ID column may have duplicates in both of the worksheets.
>
> I have never done anything in excel above the beginner level.  However, I 
> am
> a very skilled VBA programmer (programmed in ACCESS for over 10 years).
>
> Thank you 


0
12/6/2007 3:26:33 PM
Reply:

Similar Artilces:

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

Remove multiple rows of Null fields in a report
I have created a report with multiple lines of data that could be null. I set the fields to "can shrink." It does shrink the line and move it up a field but if I have multiple fields in a row it does not bring the field all the way to the top. Any ideas? Brad wrote: >I have created a report with multiple lines of data that could be null. I set >the fields to "can shrink." It does shrink the line and move it up a field >but if I have multiple fields in a row it does not bring the field all the >way to the top. A CanShrink text box will shrin...

Array to single row -- Any simple way to do this?
Is there an obvious formula to consolidate alphanumeric data across an array of multiple rows and columns into one row of values which have appeared at least once in the array (i.e., no duplicates)? Example: A B C 1 Apple Pear Orange 2 Grape Apple Pear 3 Melon Orange Grape Result: Apple, Orange, Pear, Grape, Melon I would also only like to show a value if it appears at least x times across the array. Example: (Must appear at least 2 times) Result: Apple, Orange, Pear, Grape Thanks for any and all help. CB Hi any chance you could...

Help on {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}
Dear All, Plz help on this formula {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}, how this works as I m new excel user. what does is meant $A$1:$A$7=$A$10 Hi As an array formula, it is testing each cell in the range A1 to A7 to see if it has the same value as that in cell A10 and returning what is the first row number that the value occurs in the range. -- Regards Roger Govier "Sandeep Jangra" <SandeepJangra@discussions.microsoft.com> wrote in message news:9A133D2D-75A6-4A3B-93AB-6680D164C647@microsoft.com... > Dear All, > Plz help on this formula > ...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

More visible FIND highlight
When using the Find function in a large database, the information is found but because the Find highlighting is so subdued, you still have to search a whole screen. I've tried find and excel preferences but cannot find any means of making the find highlight or colour stronger so that it stands out in the page. Appreciate suggestions, it's sending me blind. I look at the name box (to the left of the formula bar) to get the address of the activecell. You could hit the Fill icon button to change the color (then undo to put it back)? Ken Newton wrote: > > When using the Fin...

Convert Rows data
Hi there, I have the following sample data: PARTNUMBER QTY RATe PRUN VALID_FROM_VALID_TO ZWR1 0.1095 USD 1 20050512 20050526 XREZ12 0.1095 USD 1 20050527 99991231 is it possible to put this data in the following: 20050512 20050526 20050527 99991231 ZWR1 XREZ12 0.1095 0.1095 USD USD 1 1 So basically make the exis...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Insert / remove a row from protected sheet excel 2003 #2
Hi, I have my worksheet protected. I set the frist 5 rows all cells to Locked. The rest of the cells are Unlocked. When applying protection I ticked the boxes saying allow insert of row and allow delete rows. Yet when the protection is applied the icons / menu options for inserting / deleting rows are grayed out. What am I doing wrong? Thanks! Michiel. ...

how to create formula to divide two rows autoaatically
Is there any way to setup a sheet or create a formula so that it will divide the data in column A by Column B anytime the data is entered and put it into columnC ? What I am trying to do is create a spreadsheet for calculating fuel MPG. So I have columns as miles, gallons and the calculation as MPG. What I want to do is anytime a value is entered into miles and gallons, to calculate mpg and put it into that respective cell. Is there any way I can do this ? Thanks -- Tony Tony, in Column C, type the formula =sum(a1/b1). This should give you the result you are looking for. Hop...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

high light row your working in
If I'm working in a cell, is there a way to high light the entire row so you can follow it to the column you want. shift + enter "kcholly" wrote: > If I'm working in a cell, is there a way to high light the entire row so you > can follow it to the column you want. Just click on the row number in the row header. You might also want to check this out: http://www.cpearson.com/excel/RowLiner.htm -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! =========================================...

Highlight field when value is changed
I have multiple rows of data, each one starting with a number. If the number in the front of the row changes, I need that whole row to be highlighted. Then I need a check box on the other side of the row so that I can turn off (reset) the highlighting. 1 2 3 4 a # Name S Reset b 21 Jane Hanson F c 22 Mark Johnson M d 23 Fred Lee M I know I can do this with track changes, but we can't use that because of other issues. Can anyone tell me where I can get info on trying to make something like this? I t...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

change background color of row until condition is met
I have 2 columns, status and order. If the status is "T", then the whole row for that work order should have a yellow background. next time the order is entered and its status is still "T", that row should be yellow too until the status for the order becomes "C" when it bcomes "C", all the yellow rows should be back to default A B ORDER STATUS 1 T 2 C 1 T 1 C So the first and third rows should be yellow, but when i enter the fourth row and enter "C" for...

comparing cell
i have a spreadsheet that contain a column of name (some the same and some not) and would like to place each name in a row acroos the top of the same spreadsheet. Smith Jones Brown Smith Smith Brown Smith Jones Jones Smith Brown Hi Stanley First insert a heading above your column of names. Then Data>Filter>Advanced Filter mark the source as your column of names. Choose copy to another location and choose a detination cell on the sheet. Select Unique values only. Having got a unique list of names, copy this list and Paste Special>Transpo...

Compare and delete row
I need some help - I need to compare a cell, let's say A1, that wil contain a date. I need Excel to compare it to today's date, if it i over 60 days, I need it to go ahead and delete that row. If not, I nee it to leave alone. Thanks in advanc -- Message posted from http://www.ExcelForum.com Try this Sub test() If IsDate(Range("a1")) = True Then If Range("a1").Value > Date + 60 Then Range("a1").EntireRow.Delete End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "intheway >" <<intheway.176s7x@excelforum-nospam.c...

compare side by side
Using WindowsXP-Media Edition with Service Pack 2 and Office 2003. When I choose the "compare side by side" option, my two workbooks appear one above the other rather than side by side. Am I missing something?? >>Am I missing something?? I commented on that to MS during the beta test. They ignored me! -- Jim "BK" <nospam@nospam.com> wrote in message news:%233jhCdasHHA.1216@TK2MSFTNGP04.phx.gbl... | Using WindowsXP-Media Edition with Service Pack 2 and Office 2003. | | When I choose the "compare side by side" option, my two workbooks appear ...

Comparing Excel Spreadsheets
Ok, got one I can't handle.. I have one of two scenarios. Either I have 2 Excel workbooks, or 1 workbook with 2 worksheets.. Either way, I have 2 one Column data sets. I need a way to look at the data sets and find what is missing Example Data 1 = 1 2 3 4 5 Data 2 = 1 3 5 6 Results = 2 4 6 Does anyone know how to do this? Thanks Joe ...

compare time
I would like to compare a calculated time in my work sheet vs. the actual system time. For example, if the caculated time is >than or = to the system time I want excel to tell me to "CALL", if not then "REST"...any help would be appreciated. Thanks, DE Put your calculated time in a cell (say A1) In B1 put =NOW() in C1 enter =IF(A1>=B1,"CALL","REST) -- Gary''s Student "Eddie Munster" wrote: > I would like to compare a calculated time in my work sheet vs. the actual > system time. For example, if the caculated time is ...

Column comparing
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Check your earlier post. Dave T wrote: > > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > this please? > > Many thanks > DT -- Dave Peterson ...

font size of row and title heads
The column titles (A, B, C) and row titles (1, 2, 3) in my Excel are very large, even when the workbook is zoomed to 100%. Can I make them smaller? in the Format Menu, click on cells. Then click on the Font Tab and format away! "Dave B" wrote: > The column titles (A, B, C) and row titles (1, 2, 3) in my Excel are very > large, even when the workbook is zoomed to 100%. Can I make them smaller? > > > Format>style>normal, default font should be Arial 10 -- Regards, Peo Sjoblom "Dave B" <davidbarbetta@hotmail.com> wrote in message new...

Counting populated rows in excel
I have some rows populated in an excel sheet. Is there a way I can writ a macro to count the number of populated rows ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Can you pick out a column that's always filled in? If yes, then maybe you could do this: Option Explicit Sub testme() Dim LastRow As Long With Worksheets("sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With MsgBox LastRow & " is th...

count number of columns based on values across a row
i need to calcuate the number of columns that have a value greater than zero within a particular row. As stated in your previous thread: =COUNTIF(A2:Z2,">0") -- Best Regards, Luke M "r2rcode" <r2rcode@discussions.microsoft.com> wrote in message news:4381E2C3-9F78-403C-9B6E-3737C62C427F@microsoft.com... >i need to calcuate the number of columns that have a value greater than >zero > within a particular row. ...