Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Alexandrina1 pe Simpatie
 | Femeie
22 ani
Arges
cauta Barbat
22 - 46 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
1. Highlight the active Row in a defined range
2. Highlight the active Row and Column in a defined range
3. Highlight the active Row with an underlining shape (line, rectangle etc.)
4. Highlight the active Row and Column left and above of the selected cell
5. Highlight the active Row and Column with colored borders
6. Highlight multiple Rows and Columns at once
Think that you have loads of data with many rows and you need to check them daily for details, or add new data. You might miss things after concentrating on many rows too much time, clicking on a row and reading data from the row above or below.
The solutions below don't mess with previous cell fill, font color or whatever formatting you have in place.
Credits and sources:
_______________________________________
|
|
| pus acum 2 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
1. HIGHLIGHT THE ACTIVE ROW IN A DEFINED RANGE
NOTE:
THE EXCEL FILE MUST BE SAVED AS A MACRO-ENABLED DOCUMENT (XLSM).
Also: Selecting multiple rows or columns won't highlight all of them, just the one that began the selection. I might post in the future a tutorial for selecting and highlighting multiple rows, so stay tuned.
The solution below also allows you to do it multiple times in the same sheet for multiple ranges, with different highlighting options. You can do part A multiple times for multiple ranges in the same sheet. Part B only has to be done once for a sheet (multiple times if multiple sheets, once for each).
A. CONDITIONAL FORMATTING
1. Select the desired range of cells where you want the code to work - it can be for example A1:E55 or A5:AD:10000 or whatever
You can select cells with data or blank cells, it doesn't matter.
2. On the HOME tab click on Conditional Formatting and select New Rule
3. Select "Use a formula to determine which cells to format"
4. In the field enter the following formula: =OR(CELL("row")=CELL("row";A1))
Replace A1 in the formula above with the first cell of your range - for example for A5:AD10000 you have to replace A1 above with A5
5. Click the Format button and select the desired formatting - I usually use bold font and yellow fill, but you can use white font and red fill etc.
6. When ready click OK and then OK again
B. VBA
1. Right click on the sheet name and select View Code to open the Microsoft Visual Basic for Applications window
2. In the window that opens paste the following lines of code:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub |
This it how it should look like (don't worry if the left "tree" part is not showing - you can see it by going to View - Project Explorer):
3. Close the Microsoft VBA window (not the Excel file)
4. Save the file as an Excel Macro-Enabled Workbook (.XLSM)
THAT'S IT!
_______________________________________
|
|
| pus acum 2 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
2. Highlight the active Row and Column in a defined range
TBA
_______________________________________
|
|
| pus acum 1 an |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
3. Highlight the active Row with an underlining shape (line, rectangle etc.)
TBA
_______________________________________
|
|
| pus acum 1 an |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
4. Highlight the active Row and Column left and above of the selected cell
TBA
_______________________________________
|
|
| pus acum 1 an |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
5. Highlight the active Row and Column with colored borders
TBA
_______________________________________
|
|
| pus acum 1 an |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
6. Highlight multiple Rows and Columns at once
TBA
_______________________________________
|
|
| pus acum 1 an |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
Insert this by clicking just two buttons:
- 1 to insert predefined formatting (eg yellow fill and bold text) to selected range (by input box) in the active sheet
- 2 to insert some code to the active sheet's code, so that when you click on another cell (row) in the selected range, it applies the formatting to that (just like in the post above)
They can also be joined into 1 VBA module and you would have to press just 1 button.
_______________________________________
|
|
| pus acum 1 an |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
Derek Baldry's answer:
If your worksheet has coloured cells you might try using a shape to indicate the activecell row. Insert a long red line (shape) and name it "RowMarker". Then use a selection change event macro, like the one below, to move the line below your activecell. You can add controls to your macro to restrict where it is used. Here I have used cell A1 to turn it on or off.
You will have to name your line with RowMarker (just like you name a range of cells - click on the line to select it, then go to the box on the left of the formula line and type in RowMarker then press enter).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [A1].Value > 0 Then ' you must add any string in cell A1 (or a cell of your choice, just change it in this code). You can set this for a cell in your table's header that always has content in it
'ActiveSheet.Unprotect ' you have to activate this line if you activate the line with ActiveSheet.Protect (read there for more info)
ActiveSheet.Shapes("RowMarker").Top = ActiveCell.Offset(1, 0).Top
ActiveSheet.Shapes("RowMarker").Left = Range("A:A").Left ' this row makes the line start from column A, change to any other column to make it start from it
'ActiveSheet.Protect ' you can activate this in order to protect the line from being accidentally moved / removed
' it protects the sheet and you will not be able to edit any data
End If
End Sub |
_______________________________________
|
|
| pus acum 1 an |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
Thanks to DerbyDad03 from computing.net
In the following link there are more options on this matter, including a way how to highlight all selected rows using conditional formatting and VBA:
_______________________________________
|
|
| pus acum 1 an |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
To highlight both row and column, instead of the formula:
=OR(CELL("row")=CELL("row";A3)) from post #1
use this formula:
=OR(CELL("col")=COLUMN();CELL("row")=ROW())
_______________________________________
|
|
| pus acum 1 an |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
IMPORTANT!
MICROSOFT ADDED SOME SECURITY RULES THAT PREVENT YOU FROM RUNNING CODE FROM FILES THAT EXIST, FOR EXAMPLE, ON A NETWORK SERVER
|
|
| pus acum 1 luna |
|
