Posts by Marius44
-
-
Hello
As for the different color for the same series, I think you have to use VBA.
Try the following macro (to be associated with a button)
Code
Display MoreOption Explicit Private Sub Chart_Activate() Dim Ser As Series, i As Long, r As Integer, g As Integer, b As Integer Dim ptname As String Dim lngColor As Long, colR(1 To 131), colG(1 To 131), colB(1 To 131) Application.ScreenUpdating = False For i = 4 To 134 lngColor = Worksheets("Meow").Cells(i, 7).DisplayFormat.Interior.Color r = lngColor Mod &H100 lngColor = lngColor \ &H100 g = lngColor Mod &H100 lngColor = lngColor \ &H100 b = lngColor Mod &H100 colR(i - 3) = r colG(i - 3) = g colB(i - 3) = b Next i Sheets("Graph....").Activate With ActiveChart For Each Ser In .SeriesCollection For i = 1 To Ser.Points.Count ptname = WorksheetFunction.Index(Ser.XValues, i) If Ser.Name = "Actual" Then Ser.Points(i).Select With Selection.Format.Line .Visible = msoTrue .ForeColor.RGB = RGB(colR(i), colB(i), colG(i)) .Weight = 2 End With Else Exit For End If Next i Next Ser End With Application.ScreenUpdating = True End Sub
Let know.
Hello,
Mario
-
Hello
In your file, the mouse wheel works normally for me, i.e. vertical scrolling.
Hello,
Mario
-
Hello
Try this:
- in U4 = AVERAGE(I4:I8)
- IN U5 = DATA.DIFF(U4;'Run Zones'!$U$8;"y")&" Years " & DATA.DIFF(U4;'Run Zones'!$U$8;"YM") &" Months"
Bye bye
Mario
-
-
Hello
I did not understand anything.
And then why attach a VBA protected file?
Hello,
Mario
-
Hello
With VBA? Try this macro
Code
Display MoreSub CountNonZero() ur = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To ur trg = Sheets("Sheet1").Cells(i, 1).Value For j = 3 To ur cnt = Sheets("Sheet2").Cells(j, 1).Value If trg = cnt Then Rng = "B" & j & ":P" & j nn = Application.WorksheetFunction.CountIfs(Sheets("Sheet2").Range(Rng), ">0") Sheets("Sheet1").Cells(i, 4) = nn nn = 0 Exit For End If Next j Next i End Sub
Let me know. Hello,
Mario
-
Hello
Try in B2 and drag down
=COUNTIFS(Sheet2!B3:P3;">0")
Hello,
Mario
-
Hello
I followed the example given by the applicant.
Hello,
Mario
-
-
Hello
If I understand correctly I believe that the use of VBA is necessary.
A double loop to scan the cells of the first table and a counter to increment the target column.
Hello,
Mario
-
-
Hi
You have to adapt the formulas like these:
in E11 =SE(C11="";"";SE.ERRORE($F$2*(C11-$I$2);"" )) and copy to E19
in E23 =SE(C23="";"";SE.ERRORE($F$3*(C23-$I$3);"" )) and copy to E27
in E31 =SE(C31="";"";SE.ERRORE($F$4*(C31-$I$4);"" )) and copy to E45
Hi,
Mario
-
Hello
Try this
=IF(C17>0;$F$2*(C17-$I$2);"")
Hi,
Mario
-
Really so I did put in the code and test it out.. when I save it then close it out. I open it up just now. why is it not there anymore I don't get it as it suppose to stay in there. so what am I doing wrong here?
Hello
After entering the code how do you save the file? With what extension?
If the extension is different from .xlsm it warns you but does NOT save the code.
I hope I didn't say nonsense.
Hello,
Mario
-
Hello
Instead of using TRUE / FALSE use this formula for column F (the one I used) = IF (B3 <= 0.05, B3, 0).
In G3 put this formula = MAX (F3: F59)
Finally in FC enter and this formula (choosing the color you like best) = B3 = $ G $ 3 and in It applies to this reference = $ B $ 3: $ B $ 59
Hello,
Mario
-
-
Hello
Could you please attach your file (without sensitive data) with the problem.
Thanks in advance. Hello,
Mario
-
Hi
If you to compare different text (upper and lower case) you have to add this code (after Option Explicit and Before Sub RangeColor).
It is to say to add the red-row codeOption Explicit
Option Compare Text
ub RangeColor() Bye, Mario -
Hi
Try with this code (but without Format Condition)Code
Display MoreOption Explicit Sub RangeColor() Dim sh1 As Worksheet, sh2 As Worksheet Dim ur As Long, i As Long, j As Long, k As Long Dim startlimit As Variant, proxlimit As Variant, endlimit As Variant Dim aa As String, bb As String, cc As String, area_one As String, area_two As String Set sh1 = Sheets("control Panel") Set sh2 = Sheets("Master File") ur = sh2.Cells(Rows.Count, 1).End(xlUp).Row With sh1 startlimit = .Cells(9, 6).Value proxlimit = .Cells(12, 6).Value endlimit = .Cells(15, 6).Value End With For i = 2 To ur If sh2.Cells(i, 1) = startlimit Then aa = "$B$" & i For j = i + 1 To ur If sh2.Cells(j, 1) = proxlimit Then bb = "$L$" & j GoTo xit_one End If Next j End If Next i xit_one: area_one = aa & ":" & bb For k = 2 To ur If sh2.Cells(k, 1) = endlimit Then cc = "$B$" & k GoTo xit_two End If Next k xit_two: area_two = "$B$" & j + 1 & ":$L$" & k sh2.Select Range("B2:L" & ur).Interior.Color = xlNone ' Make yellow background Range(area_one).Interior.Color = 13434879 ' Make green background Range(area_two).Interior.Color = 13434828 Set sh1 = Nothing Set sh2 = Nothing End Sub
Bye,
Mario