I'm working on a spreadsheet that incorporates military date formats and needs specific formatting based upon earlier columns and the date of the cell. I have all the colors in RGB format, and I was hoping it would work, but it didn't. I don't know if it was my attempt to code it or something, probably both. Attached is an example of what I would need something to look like, and here is the VBA I had originally tried (I used several examples from across the web to put it together ^^'):
Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("G")
For Each Cell In MyPlage
If Cell.Value <= VALUE(TEXT(YEAR(TODAY()),"0000")&TEXT(MONTH(TODAY()),"00")&TEXT(DAY(TODAY()),"00")) And Cell.Value > 0 Then
If Range($D).Cell.Value = "ü" Then
Cell.Interior.Color = RGB(146,208,80)
Else If Range($D).Cell.Value = "" And Range($C).Cell.Value = "ü" Then
Cell.Interior.Color = RGB(224,224,0)
Else If Range($C:$D).Cell.Value = "" And Range($B).Cell.Value = "ü" Then
Cell.Interior.Color = RGB(204,204,0)
Else If Range($B:$D).Cell.Value = "" And Range($A).Cell.Value = "ü" Then
Cell.Interior.Color = RGB(184,184,0)
Else If Range($A:$D).Cell.Value = "" Then
Cell.Interior.Color = RGB(224,0,0)
End If
End If
If Cell.Value < VALUE(TEXT(YEAR(TODAY()),"0000")&TEXT(MONTH(TODAY()),"00")&TEXT(DAY(TODAY()),"00")) Then
Cell.Interior.Color = RGB(204,0,0)
End If
If Cell.Value = "" Then
Cell.Interior.Color = RGB(0,0,0)
End If
Next
End Sub
Display More
The VALUE(TEXT(YEAR(TODAY()),"0000")&TEXT(MONTH(TODAY()),"00")&TEXT(DAY(TODAY()),"00")) is my own creation to format the value to how I have to have the date entered into the cells. I've already confirmed this works (basic functions is something I'm actually good with ^^).
Here is a basic break-down of how I need it to work:
If 'Date 2'.Cell.Value <= Today() And 'Date 2'.Cell.Value > 0 And 'Date 1'.Cell.Value <= Today() And 'Date 1'.Cell.Value > 0 Then
If 'Column D'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(146,208,80)
'Date 2'.Cell.Interior.Color = RGB(146,208,80)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(224,224,0)
'Date 2'.Cell.Interior.Color = RGB(224,224,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(204,204,0)
'Date 2'.Cell.Interior.Color = RGB(204,204,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "" And 'Column A'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(184,184,0)
'Date 2'.Cell.Interior.Color = RGB(184,184,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "" And 'Column A'.Cell.Value = "" Then
'Date 1'.Cell.Interior.Color = RGB(224,0,0)
'Date 2'.Cell.Interior.Color = RGB(224,0,0)
End If
Else If 'Date 2'.Cell.Value <= Today() And 'Date 2'.Cell.Value > 0 And 'Date 1'.Cell.Value = "" Then
If 'Column D'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(0,0,0)
'Date 2'.Cell.Interior.Color = RGB(146,208,80)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(0,0,0)
'Date 2'.Cell.Interior.Color = RGB(224,224,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(0,0,0)
'Date 2'.Cell.Interior.Color = RGB(204,204,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "" And 'Column A'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(0,0,0)
'Date 2'.Cell.Interior.Color = RGB(184,184,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "" And 'Column A'.Cell.Value = "" Then
'Date 1'.Cell.Interior.Color = RGB(0,0,0)
'Date 2'.Cell.Interior.Color = RGB(224,0,0)
End If
Else If 'Date 2'.Cell.Value > Today() And 'Date 1'.Cell.Value <= Today() And 'Date 1'.Cell.Value > 0 Then
If 'Column D'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(146,208,80)
'Date 2'.Cell.Interior.Color = RGB(224,0,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(224,224,0)
'Date 2'.Cell.Interior.Color = RGB(224,0,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(204,204,0)
'Date 2'.Cell.Interior.Color = RGB(224,0,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "" And 'Column A'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(184,184,0)
'Date 2'.Cell.Interior.Color = RGB(224,0,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "" And 'Column A'.Cell.Value = "" Then
'Date 1'.Cell.Interior.Color = RGB(224,0,0)
'Date 2'.Cell.Interior.Color = RGB(224,0,0)
End If
Else If 'Date 2'.Cell.Value = "" And 'Date 1'.Cell.Value <= Today() And 'Date 1'.Cell.Value > 0 Then
If 'Column D'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(146,208,80)
'Date 2'.Cell.Interior.Color = RGB(0,0,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(224,224,0)
'Date 2'.Cell.Interior.Color = RGB(0,0,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(204,204,0)
'Date 2'.Cell.Interior.Color = RGB(0,0,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "" And 'Column A'.Cell.Value = "ü" Then
'Date 1'.Cell.Interior.Color = RGB(184,184,0)
'Date 2'.Cell.Interior.Color = RGB(0,0,0)
Else If 'Colum D'.Cell.Value = "" And 'Column C'.Cell.Value = "" And 'Column B'.Cell.Value = "" And 'Column A'.Cell.Value = "" Then
'Date 1'.Cell.Interior.Color = RGB(224,0,0)
'Date 2'.Cell.Interior.Color = RGB(0,0,0)
End If
Else If 'Date 2'.Cell.Value = "" And 'Date 1'.Cell.Value = "" Then
'Date 1'.Cell.Interior.Color = RGB(0,0,0)
'Date 2'.Cell.Interior.Color = RGB(0,0,0)
End If
Display More
I know, my attempt at the actual code looks nothing like that. My attempt was also trying to simplify it, which is where I think I screwed up.
Anyways, anyone think they can help?
Oh, about the "ü" thingies (in Wingdings font they're check marks), it's in order of precidence, so if I have a check in column D, I don't care about A-C. Basically, the highest check will determine the color.
Thank you in advanced for simply looking at this.