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:
oanacreatza pe Simpatie.ro
 | Femeie
21 ani
Timis
cauta Barbat
25 - 55 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 18 ani
Postari: 2247
|
|
A user on excelforum.com had a range of required material by date and a total stock. He wanted to color the cells red from the day when he would start to run out of stock - color red if date = stock - 2 pcs.
Made him the following macro to insert conditional formatting (credits for the formula to fshbet) to a selected range in order to color the cells red (macro makes it possible to have a variable range, doesn't matter how many rows or columns):
Sub Add_Conditional()
Application.ScreenUpdating = False
Dim rng As Range
Set rng = Selection
Dim lastCol As Long
Let lastCol = rng.Cells(rng.count).Column
rng.FormatConditions.Add Type:=xlExpression, _
Formula1:="=IF(SUM(" & rng.Cells(1, 1).Address(False, True) & ":" & rng.Cells(1, 1).Address(False, False) & ")>" & rng.Cells(1, lastCol).Address(False, True) & "-2;TRUE;FALSE)"
rng.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
With rng.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
rng.FormatConditions(1).StopIfTrue = False
Application.ScreenUpdating = True
End Sub |
Source:
_______________________________________
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
Source:
Dim Switch As Boolean
For Each Cell In Range("B2:B" & ActiveSheet.UsedRange.Rows.Count)
If Not Cell.Value = Cell.Offset(-1, 0).Value Then Switch = Not (Switch)
If Switch Then Range("A" & Cell.Row & ":" & Chr(ActiveSheet.UsedRange.Columns.Count + 64) & Cell.Row).Interior.Pattern = xlNone
If Not Switch Then Range("A" & Cell.Row & ":" & Chr(ActiveSheet.UsedRange.Columns.Count + 64) & Cell.Row).Interior.Color = 14869218
Next |
|
|
| pus acum 5 ani |
|