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:
Cosminamoraru pe Simpatie.ro
 | Femeie
25 ani
Bacau
cauta Barbat
30 - 80 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
THIS IS MADE OF 3 SUBS, THE BUTTON SHOULD BE SET TO RUN THE FIRST WHICH IS Z_AddCondFormatToRng
1st code adds conditional formatting, 2nd code adds code to active sheet, 3rd code deletes code in active sheet if there is any, because if 2nd code gets inserted twice errors emerge
This is the sum of 2 steps (2 topics):
Step 1 - Insert Conditional Formatting with Formula [VBA] & Step 2 - Insert VBA Code into Active Sheet Code [VBA]
And the result is:
Insert the following code into a module in personal.xlsb, then create a button for it in Quick Access Toolbar so the result in the topic above is available by 1 click:
Sub Z_AddCondFormatToRng()
Application.ScreenUpdating = False
Dim rng As Range
With ActiveSheet
Set rng = Application.InputBox(prompt:="Selecteaza zona in care doresti formatare conditionata:", Type:=8)
On Error Resume Next
If rng Is Nothing Then
MsgBox "cancel pressed"
Else
rng.Select
Dim ActiveCell As String
ActiveCell = Excel.ActiveCell.Address(0, 0)
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(CELL(""row"")=CELL(""row"";" & ActiveCell & "))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535 'or use RGB: .Color = RGB(255, 0, 0) for color red for example
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Font
.Bold = True
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
End With
Application.ScreenUpdating = True
Z_AddCodeToSht
End Sub
' You need to go to TOOLS - REFERENCES and add the following library:
' Microsoft Visual Basic for Applications Extensibility 5.3
Sub Z_AddCodeToSht()
Dim wb As Workbook
Dim ws As Worksheet
Dim xPro As VBIDE.VBProject
Dim xCom As VBIDE.VBComponent
Dim xMod As VBIDE.CodeModule
Dim xLine As Long
Set wb = ActiveWorkbook
Set ws = ActiveSheet
With wb
Set xPro = .VBProject
Set xCom = xPro.VBComponents(ws.CodeName) '.CodeName for VB sheet name; .Name for Excel tab names
Set xMod = xCom.CodeModule
Z_RemoveVBAinActiveSheetCode
With xMod
xLine = .CreateEventProc("SelectionChange", "Worksheet")
xLine = xLine + 1
.InsertLines xLine, " Target.Calculate"
End With
End With
End Sub
Sub Z_RemoveVBAinActiveSheetCode()
Dim activeIDE As Object 'VBProject
Set activeIDE = ActiveWorkbook.ActiveSheet.VBProject
Dim Element As VBComponent
Dim LineCount As Integer
For Each Element In activeIDE.VBComponents
If Left(Element.Name, 5) = "Sheet" Then 'change name if necessary
LineCount = Element.CodeModule.CountOfLines
Element.CodeModule.DeleteLines 1, LineCount
End If
Next
End Sub |
_______________________________________
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
Actually they're 3 buttons. See below.
This achieves the same result as in the following topic:
With a few important observations:
- PRO: you no longer need to save your file as a macro-enabled workbook - for security reasons, you might not even want to
- PRO: you don't have to save the file at all if it's a temporary file (opened from an e-mail for example)
- PRO: you can easily add the conditional formatting to cells, the VBA code to the sheet/sheets and afterwards erase all VBA codes with a total of 3 mouse clicks (actually 4 because you would have to choose the range to apply conditional formatting to also using 1 mouse click)
- CON: the conditional formatting style is preselected via VBA (1st code below), but you can either edit it afterwards or edit the VBA code as per your needs - no need to have knowledge about VBA programming
What do the 3 codes below do?
1. 1st code inserts predefined conditional formatting to a range of cells of your choosing
- after clicking it an input box will appear and you must select the range of cells you want the conditional formatting applied to
2. 2nd code inserts the vba part from my 1st post (link above) to the active sheet (only to the active sheet)
- if you want this on multiple sheets go on them and click the button
- note that pressing this button will open a VBA window (which you can close without problems)
- you can add this code for as many sheets as you want
3. 3rd code removes any vba in your current workbook (from all the sheets)
VERY IMPORTANT!
To have access to all 3 codes from any workbook you use, you must create the VBA modules inside your PERSONAL.XLSB file.
To learn how to create it, see this topic:
Create 3 modules in your PERSONAL.XLSB file under the Modules 3 tree, then insert each of the following codes to a module.
ALSO VERY IMPORTANT
For the 2nd and the 3rd codes to work, you will need to activate a library in VB editor.
While in VB editor having the VBAProject(PERSONAL.XLSB) tree selected, go to TOOLS - REFERENCES and scroll down the list to find Microsoft Visual Basic for Application Extensibility 5.3. Check it and click OK.
After you copy/paste the codes below in all your modules and save your PERSONAL.XLSB file, check steps 5 and 6 in the topic below to learn how to add them as buttons in your Quick Access Toolbar:
Sub Z_1_InsertCondFormat()
Application.ScreenUpdating = False
Dim rng As Range
With ActiveSheet
On Error Resume Next
Set rng = Application.InputBox(prompt:="Select the area / range where you want conditional formatting to be applied:", Type:=8)
If rng Is Nothing Then
MsgBox "Selection cancelled."
Else
rng.Select
Dim ActiveCell As String
ActiveCell = Excel.ActiveCell.Address(0, 0)
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(CELL(""row"")=CELL(""row"";" & ActiveCell & "))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535 'or use RGB: .Color = RGB(255, 0, 0) for color red for example
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Font
.Bold = True
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
End With
Application.ScreenUpdating = True
End Sub |
....
' For this to work you need to go to TOOLS - REFERENCES and add the following library:
' Microsoft Visual Basic for Applications Extensibility 5.3
Sub Z_2_AddVBAToActiveSht()
Dim wb As Workbook
Dim ws As Worksheet
Dim xPro As VBIDE.VBProject
Dim xCom As VBIDE.VBComponent
Dim xMod As VBIDE.CodeModule
Dim xLine As Long
Set wb = ActiveWorkbook
Set ws = ActiveSheet
With wb
Set xPro = .VBProject
Set xCom = xPro.VBComponents(ws.CodeName) '.CodeName for VB sheet name; .Name for Excel tab names
Set xMod = xCom.CodeModule
With xMod
xLine = .CreateEventProc("SelectionChange", "Worksheet")
xLine = xLine + 1
.InsertLines xLine, " Target.Calculate"
End With
End With
End Sub |
....
' For this to work you need to go to TOOLS - REFERENCES and add the following library:
' Microsoft Visual Basic for Applications Extensibility 5.3
Sub Z_3_RemoveVBAinActiveWorkbook()
Dim activeIDE As Object 'VBProject
Set activeIDE = ActiveWorkbook.VBProject
Dim Element As VBComponent
Dim LineCount As Integer
For Each Element In activeIDE.VBComponents
If Left(Element.Name, 5) = "Sheet" Then 'change name if necessary
LineCount = Element.CodeModule.CountOfLines
Element.CodeModule.DeleteLines 1, LineCount
End If
Next
End Sub |
_______________________________________
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
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 5 ani |
|