Forum Discussion
Solved
VBA statement with a IF
Hi everyone,
i not a advanced in VBA on excel and i need your help.
I need to create a if statement that, when a colunm called "Last update time", have more then 30 days, the interior color of this cells turns to yellow, if more then 60 days, turns to red, but ir another column named "assigment group" has a specific group, the color of the colunm don´t change.
Sub color() Dim i As Integer Range("D:D").Interior.ColorIndex = 0 For i = 2 To 27 If Cells(i, 4).Value < Date - 60 And Cells(i, 5) <> "specific group" Then Cells(i, 4).Interior.ColorIndex = 3 Else If Cells(i, 4).Value < Date - 30 And Cells(i, 5) <> "specific group" Then Cells(i, 4).Interior.ColorIndex = 6 Else End If End If Next i End SubMaybe with these lines of code. In the attached file you can click the button in cell H2 to start the macro.
Sub color() Dim i As Integer Range("D:D").Interior.ColorIndex = 0 For i = 2 To 27 If Cells(i, 4).Value < Date - 60 And Cells(i, 5) <> "specific group" Then Cells(i, 4).Interior.ColorIndex = 3 Else If Cells(i, 4).Value < Date - 30 And Cells(i, 5) <> "specific group" Then Cells(i, 4).Interior.ColorIndex = 6 Else End If End If Next i End SubMaybe with these lines of code. In the attached file you can click the button in cell H2 to start the macro.
- THank you for this!
This is help me a lot in my day-to-day!
You don't need VBA for that, it can be done with conditional formatting. See the attached demo workbook.
Select Conditional Formatting > Manage Rules... on the Home tab of the ribbon to inspect the rules.
THank you for you answer.
But i have a drop down list row in the column "assigment group" and when i change the group there, the color doesn´t change like your demo spreadsheet.
Here is a version with a data validation drop-down in the Assignment Group column.
