Hi.
I have posted something similar before but have yet to make my code work a 100% yet.
MOST OF THE CODE WORKING!
I put in a lot of code so just want to mention most are working but the addition have not been working as indented.
I have a large worksheet in which there is a few macros running.
One of them is a conditional formatting of some of the columns.
In my column "AD" the cell interior is colored based on whether the status is "open, closed, on hold, N/A, TBD". (This code is working)
In my column "Y" the cell interior is colored based on on due dates and how close they are to the due date (This is working, BUT is missing a feature that I could not get to work. I wanted to add the feature that if the cell did not have any dates in the cell then the cell interior becomes transparent or xlNone.)
My main problem is another formatting is overlapping with the other two. The purpose is to have the status column "AD" have the feature so that if certain statuses such as "Closed" is in the cell, then the cell interior of column "Y" with due dates goes grey. And if the "AD" status is "on hold" then the cell interior and font color of column "Y" with due dates goes blue and red for example.
This formatting should be the one that overrules the other one (middle one).
Currently it is working for a split second but it seems like on formatting overrules the other and changes the color back. So it is like a priority issue i think.
My codes are split between both module and worksheet.
I was thinking it would be nice to have it all in one place like module but I also don't know if that is even possible.
My codes can be seen below, i hope someone can help.
I have posted something similar before but have yet to make my code work a 100% yet.
MOST OF THE CODE WORKING!
I put in a lot of code so just want to mention most are working but the addition have not been working as indented.
I have a large worksheet in which there is a few macros running.
One of them is a conditional formatting of some of the columns.
In my column "AD" the cell interior is colored based on whether the status is "open, closed, on hold, N/A, TBD". (This code is working)
In my column "Y" the cell interior is colored based on on due dates and how close they are to the due date (This is working, BUT is missing a feature that I could not get to work. I wanted to add the feature that if the cell did not have any dates in the cell then the cell interior becomes transparent or xlNone.)
My main problem is another formatting is overlapping with the other two. The purpose is to have the status column "AD" have the feature so that if certain statuses such as "Closed" is in the cell, then the cell interior of column "Y" with due dates goes grey. And if the "AD" status is "on hold" then the cell interior and font color of column "Y" with due dates goes blue and red for example.
This formatting should be the one that overrules the other one (middle one).
Currently it is working for a split second but it seems like on formatting overrules the other and changes the color back. So it is like a priority issue i think.
My codes are split between both module and worksheet.
I was thinking it would be nice to have it all in one place like module but I also don't know if that is even possible.
My codes can be seen below, i hope someone can help.
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Name the range
Dim Color_Range As Range
'Define the range
Set Color_Range = Worksheets("Actionlog").Range("Y4:Y300")
' Loop through each cell in the changed range
For Each cell In Color_Range
' Check if the date is overdue (today or earlier)
If cell.Value <= Date Then
' Color the cell red
cell.Interior.Color = RGB(255, 142, 142) ' Red
ElseIf cell.Value <= Date + 365 And cell.Value > Date + 7 Then
' Color the cell Green
cell.Interior.Color = RGB(143, 220, 178) ' Green
ElseIf cell.Value <= Date + 7 Then
' Color the cell Orange
cell.Interior.Color = RGB(242, 204, 89) ' Orange
End If
Next cell
End Sub
' The below code is overlapping the on above and gets overwritten when in fact it should be the one set as first priority'
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CCell As Range
Dim sht As Worksheet
Set CCell = Range("AD4:AD300")
Set sht = Worksheets("Actionlog")
If Target.Count = 1 Then
If Not Application.Intersect(CCell, Range(Target.Address)) _
Is Nothing Then
If Target.Value = "Closed" Then
sht.Cells(Target.Row, 25).Interior.Color = RGB(155, 155, 155)
End If
End If
End If
End Sub
'All the code below is placed in a module opposed to the code above'
'Also below code all working'
'the above code is my new code that im adding to the excel'
Sub Check_status()
Application.ScreenUpdating = False
Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition, cond4 As FormatCondition, cond5 As FormatCondition, cond6 As FormatCondition
Set rg = Range("A4:AD300", Range("A4:AD300").End(xlDown))
'clear any existing conditional formatting
rg.FormatConditions.Delete
'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Closed")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Open")
Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "On hold")
Set cond4 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Done")
Set cond5 = rg.FormatConditions.Add(xlCellValue, xlEqual, "TBD")
Set cond6 = rg.FormatConditions.Add(xlCellValue, xlEqual, "N/A")
'define the format applied for each conditional format
'Green - RBG(143,220,178), Purple - RGB (178, 162, 197), Grey - RGB(155, 155, 155), Red - RGB(255, 142, 142), Orange - RGB(242, 204, 89), Blue - RGB(87, 193, 231)
With cond1
.Interior.Color = RGB(143, 220, 178)
.Font.Color = vbBlack
End With
With cond2
.Interior.Color = RGB(255, 142, 142)
.Font.Color = vbWhite
End With
With cond3
.Interior.Color = RGB(242, 204, 89)
.Font.Color = vbRed
End With
With cond4
.Interior.Color = RGB(87, 193, 231)
.Font.Color = vbWhite
End With
With cond5
.Interior.Color = RGB(178, 162, 197)
.Font.Color = vbBlack
End With
With cond6
.Interior.Color = RGB(155, 155, 155)
.Font.Color = vbBlack
End With
Application.ScreenUpdating = True
End Sub
