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:
laura84 Profile
 | Femeie
24 ani
Valcea
cauta Barbat
24 - 49 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Additional to no. 2 above, you could add 4 colors (or even more) to your data bar range.
Eg. in cell E3 I want data bar to be:
- Red below or equal to 50%
- Yellow above 50% and below or equal 90%
- Green above 90% and below or equal to 95%
- Orange above 95% and below 100%
- Reg above or equal to 100%
1. Open up VBE, paste the following code in a module, then run it. Replace range with your own.
NOTE: For the data bar to work correctly (show the right length according to percentage) you will have to do one step additional to running the code.
Sub DataBarsAddMultiple()
With Range("C3")
.Select ' The .Formula below requires .Select here
.FormatConditions.Delete
.FormatConditions.AddDatabar
.FormatConditions(1).BarColor.Color = RGB(255, 0, 0)
.FormatConditions(1).BarFillType = xldatabarsolid
.FormatConditions.AddDatabar
.FormatConditions(2).BarColor.Color = RGB(255, 255, 0)
.FormatConditions(2).BarFillType = xldatabarsolid
.FormatConditions.AddDatabar
.FormatConditions(3).BarColor.Color = RGB(0, 255, 0)
.FormatConditions(3).BarFillType = xldatabarsolid
.FormatConditions.AddDatabar
.FormatConditions(4).BarColor.Color = RGB(255, 153, 0)
.FormatConditions(4).BarFillType = xldatabarsolid
.FormatConditions.AddDatabar
.FormatConditions(5).BarColor.Color = RGB(255, 0, 0)
.FormatConditions(5).BarFillType = xldatabarsolid
.FormatConditions(1).Formula = "=IF(C3<=50%;True;False)"
.FormatConditions(2).Formula = "=IF(AND(C3>50%;C3<=90%);True;False)"
.FormatConditions(3).Formula = "=IF(AND(C3>90%;C3<=95%);True;False)"
.FormatConditions(4).Formula = "=IF(AND(C3>95%;C3<100%);True;False)"
.FormatConditions(5).Formula = "=IF(C3>=100%;True;False)"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=1"
.FormatConditions(6).Font.Bold = True
.FormatConditions(6).Font.ThemeColor = xlThemeColorDark1
End With
End Sub
|
2. I formatted cells O1 and P1 as percentage and added 0% and 100% respectively. This is step 2 in the post above.
3. Select cell C3, go to Conditional Formatting -> Manage Rules and for each Applies to: field of all data bars add range O1:P1.
Eg. I have =$C$3 and I add semicolon, so it's =$C$3; then select range O1:P1, then click apply. I do this for all 4 data bars, then click OK.
Or make it as stated above for the first data bar, then copy ;$O$1:$P$1 and paste it after each =$C$3 (works a bit faster).
Whatever method I tried directly from VBA to add the O1:P1 as a formatted range together with C3, it wouldn't work OK. I tried Union and using a named range.
Source:
|
|
| pus acum 5 ani |
|