Free Microsoft Excel 2013 Quick Reference

How to Highlight Two Rows With Two Columns of Duplicate Values

I need to know how to highlight two rows who each have duplicate values in
two specific columns? Thanks


Hello,
Does anybody know how to display the Row and Column of an Active Cell and
display it in another cell.

Regards,

I found the code below on http://www.xcelfiles.com/Excel02.html. It is so close to what I need to do. Dear Juan Pablo, if you read this post, I would love your help in making a little adjustment to the code you have below.

It highlights active row and column, but it overwrites the already colored cells. I need it to highlight active row and column while it keeps the already highlighted cells in their original color. So, say I have some cells in yellow. When the code highlights the active row and column in (say) purple, I would like to be able to see the the whole row and column in purple while the yellow cells remain in yellow.

So can anyone tell me how to make the code below keep the original cell color of the already highlighted cells while it highlights the rest of the cells in the active row and column? Pleasee help?

Thank you!
Option Explicit
'/////////////////////////////////////////////////////
'// Amended 14th Feb 2003 - suggestion by Juan Pablo G.
'// International versons may NOT recognise TRUE
'// Suggestion use =1 which evaluates to TRUE,
'// in fact any number that <> 0
'////////////////////////////////////////////////////

Const iInternational As Integer = Not (0)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
'// Leave On Error ON for Row offset errors

If iColor < 0 Then
    iColor = 36
Else
    iColor = 36 

End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1

Cells.FormatConditions.Delete

'// Horizontal color banding
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

'// Vertical color banding
With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address)
    .FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub


Is there anyway to highlight the row and column of the active cell without permanently removing either the conditional formatting in the highlighted row and column or the colour formats? I have the following code:

Private Sub
Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
Rows(Target.Row).Interior.ColorIndex = 6
Columns(Target.Column).Interior.ColorIndex = 6
End Sub
But this gets rid of all conditional formatting in the active sheet, not ideal. Also, when I delete the code to stop using it the last row and column that was highlighted yellow remains yellow.

I know this might be a bit to ask but can anyone produce VBA code that will full fill my wish of being able to highlight the row and column of the active cell without ruining colour formatting?

Thanks in advance

I'm using Excel 2003, can u plz help me out that how to highlight current row
& column with a color automatically to identify my position. Though excel
help us by highlighting column & row number, but i want whole column & row to
be highlighten to better finding my cell position and contents. So When I
change my cell position, the highlighted row & column move appropriately.

I'm using Excel 2003, my question is how to highlight current row & column
with a color automatically, to identify my position. Though excel help us by
highlighting column & row number, but i want whole column & row to be
highlighten to better finding my cell position and all the contents in
appropriate row & column. So When I change my cell position, the highlighted
row & column move appropriately.

I'm using Excel 2003, can u plz help me out that how to highlight current row
& column with a color automatically to identify my position. Though excel
help us by highlighting column & row number, but i want whole column & row to
be highlighten to better finding my cell position and contents. So When I
change my cell position, the highlighted row & column move appropriately.

I want excel to shade the row and column of my active cell. As my active
cell changes, so will the shaded row and column.

Hello!
How to copy a row with transposing to a column retaining links to input
cells? Excel transposes with a links change or only values.
Best regards,
Dima
+7 9163876746

how to delete unwanted rows after extracting top 10 values in excel

Hi,
I was wondering if there is another simplier approach to doing this as I need to extract the top 10 scores of students from each different groups , and then deleted the unwanted rows (e.g. 1th rows onwards).
Some of the groups, may have less than 20 students,though majority of these have > 20 in each different groups in rows and I have a numerous records to go thru such as attached..

Group Names ID Scores(Mean) Median Subject
1st.. A01

10th
11th
1st.. B02

10th
11th
... C03 .....
..........

Presently ,I first perform sorting by Group 1st, then by Scores( Largest to smallest) ,then goto each group in their first sorted row and manually numbered 1 to 20 in each group, and delete the rest of the entries in each corresponding group…So, if there is a faster way and efficient way via formula or VBA to perform this automatically…Please advise as i have quite a number of rewcords to do for the same steps....

Thks.
Regds,
Leonard
….

How can i highlight the row if the value of a cell is equal "Apple".

Highlight:
example column E:E has a value of "Apple" or "Grapes" then i wanted to highlight the row with a red if the value of a cell is "apple"...help

Hide a row:
Same example of the above but this time i wanted to hide a row if the value of a cell is = "Apple"...

Thanks

Friends and Experts,

My colleguaes and I work with a rather large data entry/archiving Excel 2007 spreadsheet on a daily basis. It contains several columns and rows that will not easily fit on one viewable screen. We have searched for the ability to highlight the entire data set across the row and down the column of the selected cell. This would allow us to navigate with the directional keys quickly and enter data assuring us we are on the correct row and column with a simple visual indication.

I know that the number and letter of the row and column highlight on their own, but what we are needing is for the entire row and column of data to highlight as well.

I have attached an example of what we would like to see.

Does anyone know of a setting/function/method to accomplish this? I have searched around to no avail.

I found this code to highlight the active row. I tried to make it highlight the row and column, but I was not successful. What I really need is to highlight the active row and column above and to the left of the active cell, not the entire row and column. For example, if G10 is active, the highlighted cells would be G1:G10 and A10:G10.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel. Range)
Dim i As Long
Cells.Interior.ColorIndex = xlColorIndexNone
If Application. CountA(Target.EntireRow) 0 Then
i = Target.Row
Else
For i = Me.UsedRange.Rows.Count To 1 Step -1
If Application.CountA(Me.Rows(i)) 0 Then
i = i + 1
Exit For
End If
Next i
End If
Rows(i).Interior.ColorIndex = 6
End Sub
Also, I have fill colors on the sheet and I just noticed that the code removes those fill colors. I need it to not remove my fill colors. The only fill colors it should remove are ones it previously colored.

Your help will be greatly appreciated.

-- DJ

For example I need to highlight all rows containing 'TODO' anywhere in the
text.
I've tried conditional formating with formula condition '=FIND("TODO">1'
but failed.
I'm looking for 2 answers:
1) how to represent the current cell reference in the formula. Is the simple
omitting (as shown above) correct?
2) how to apply the format to the whole row, not just to the cell meeting
the condition?

Thank in advance for your help!
--Josef

Hi,

I'm a new poster here, but I really need help in this matter urgently.

I'm trying to create a macro that would automatically sieve out all the rows with the largest 250 values in a particular column, say, column c. i.e, there are like 2000 'companies' in column a, and their company codes in column b. in column c, their size. I would like to remove the top 250 largest companies when i click a particular button, and have it automatically copy and pasted to another sheet entitled 'Macro Results'.

Thanks~ Please help=x

I tried using record macro but the sort in descending values option doesn't work right.

I have a list in column with words that have different number of duplicates and i want to highlight only one cell that has duplicates. For example, these 3 words:

google
bing
yahoo

Let say google is repeated 5 times along the column, bing - 4 times, yahoo - 7 times.
Is it possible to highlight only one cell that contains duplicate value? For example, highlight one cell that contain "google" not all 5 cells, one that contain "bing" not all 4 cells, and one that contain "yahoo" not all 7 cells.

Thanks!

How to filter certain row when column contain certain value using vba? i try to manually add in filter using Excel, but it doesnt works, dont know why, anyone know how to write vba code to filter out rows of which some of its columns contain certain value?

Thanks

I need to find the Row and Column of the value.
As in the below case Row 5 and column 1, if value is 8 then Row 3 Col 3

1 11 6 26 16 21
2 12 7 27 17 22
3 13 8 28 18 23
4 14 9 29 19 24
5 15 10 30 20 25

if value is 5
Find Row of value
Find Column of value

Is there any simple way to insert blank rows between two rows with different numbers!

Eg:

column a:

23
34
37
56

i want to insert blank rows between 23 and 34 rows which is around 10 blank rows , and again 2 blank rows between 34 and 37 and so on ..

Another situation is sometime i have two same value

column a:

23
34
34
37
56

In this, i dont want any blank rows between 34 and 34

How this can be done !

Many thanks in advance !

Dear All

Whatever cell I select, I want to hightlight the relative row and column of
that cell, how?

Thanks in advance.

I have an XML file(1) with structure like which I can open with Excel 2007, edit and save as xml file OK:
<?xml version="1.0"?>
<CONNECTION>
<INVENTORY_STATUS_CODE>None</INVENTORY_STATUS_CODE>
<PRIORITY>2009/07/07 13:18</PRIORITY>
<CONNECTOR_TYPE_NAME>Unspecified</CONNECTOR_TYPE_NAME>
<TRAY_NUMBER>0</TRAY_NUMBER>
<DB_LOSS>0</DB_LOSS>
</CONNECTION>

But when I try to work with XML file(2) with structure like:
<?xml version="1.0"?>
<CONNECTION>
<INVENTORY_STATUS_CODE>None</INVENTORY_STATUS_CODE>
<PRIORITY>2009/07/07 13:18</PRIORITY>
<CONNECTOR_TYPE_NAME>Unspecified</CONNECTOR_TYPE_NAME>
<TRAY_NUMBER>0</TRAY_NUMBER>
<DB_LOSS>0</DB_LOSS>
<GEOMETRIES>
<GEOMETRY>X="123" Y=123"</GEOMETRY>
<GEOMETRY>X="124" Y=124"</GEOMETRY>
</GEOMETRIES>
</CONNECTION>
I could import and edit XML file in Excel but could not export to XML.
It throws an exeption that it could not export due to XML source containing List of lists.

Does anybody have an idea how to import/map/edit/export XML file(2) correctly?

PS: I see two rows per connection in Excel with GEOMETRY column only difference.

Thanks,
Sergiy

Is there any simple way to insert blank rows between two rows with different numbers!

Eg:

column a:

23
34
37
56

i want to insert blank rows between 23 and 34 rows which is around 10 blank rows , and again 2 blank rows between 34 and 37 and so on ..

Another situation is sometime i have two same value

column a:

23
34
34
37
56

In this, i dont want any blank rows between 34 and 34

How this can be done !

Many thanks in advance !

How to highlight a row and its previous row for every blank cell in a column

I could only highlight the row containing the blank cell, but not the row above this. Help with my code. Offset doesn't seem to work for me in this scenario.


	VB:
	
 ConditionalFormatter() 
    [F3].Activate 
    With Range([F3], [F65536].End(xlUp)).EntireRow 
        .FormatConditions.Delete 
        .FormatConditions.Add Type:=xlExpression, Formula1:= _ 
        "=$F3=""""" 
        .FormatConditions(1).Interior.ColorIndex = 6 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am struggling with the following problem:
When I have two three rows with in the first column a name and in the second column a quantity for example:
A 10
B 12
A 14

I want to calculate the sum of the rows where the name is A, whit which function can I determine the sum (in this example 24).

Kind regards,

Bram.

I am trying to compare two worksheets with one having relatable data (see
illustration below-Item). The relatable field has row information which is
pertinent to the 'comparable' field.
Sheet # 2 is the 'master-data' ; using gathered referneced data entered in
sheet # 1 , I am trying to derrive 'matching results and 'unmatching results'
Tried using 'pivot table ; did not receivie likeable results. Is their a
'macros' I could use????
(Please see illustration below)

Sheet # 1 -Entered data
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 111 Egg 10 11 12
001 222 Fruit 12 14 22
001 333 bread 2 12 24

Sheet #2 - Static Data
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
002 333 bread 1 10 5
002 222 Fruit 10 12 20
002 111 Egg 10 11 12
002 444 Drink 4 4 4

Would like results of.......
Sheet # 3 - Matching
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 111 Egg 10 11 12

Sheet # 4 - Not Matching
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 222 Fruit 12 14 22
001 333 bread 2 12 24
002 444 Drink 4 4 4