Must have VBA for conditional formatting: If value of below cell differes from above, color entire row in green

[Lea82]

Good day everybody!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

I got an urgent excel problem at work and I hope someone will help me solve it.
I have to mention though that I don’t have any clue about VBA AT ALL, so please be patient with me.
<o></o>

Following Problem:
I have a file to keep record of various projects for each month.
In column A the users enter the current month (January projects, February projects…).
As soon as they start entering a new month for the first time, I want that the cell backgrounds of the entire row gets colored light green and the month (e.g. A2) turns into bold font.
So basically IF A2<>A1, color A2:W2 in light green.
<o></o>
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>
<o><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype></o>
<o></o>

MONTH | PROJECT NO. | STATUS<o></o>
JAN <o></o>
JAN
JAN <o></o>
FEB <o></o>
FEB <o></o>
FEB <o></o>
MAR <o></o>
MAR


Since I am already using other conditional formatting in all cells (e.g. IF C2=closed change font color in row 2 to grey, IF C2=cancelled, change it to red), it doesn’t work if I add this as 3<SUP>rd</SUP> condition. Because this way if it turns to green, then my font will turn automatically to black, even if it’s a cancelled project.
So I think I need a VBA for this one.
<o></o>

Can someone please please help me and also tell me where to enter the VBA in my workbook and how have it automatically applied?
<o></o>

Thanks so much, your help is really appreciated!
<o></o>
Ciao Lea

PS: Sorry, I couldn't figure out how to insert a picture in my post...

 

[Gary McMaster]

Hi Lea,

I'm not sure this will sit well with your existing conditional formatting. If not, maybe the conditional formatting can be made to work despite the obstacles you have mentioned. The formatting could also be done entirely with code instead of conditional formatting if necessary. I don't really understand what your problem with the conditional formatting is.

In any case, you can try the sample below as follows:

Right click on one of the sheet tabs and select "View Code" from the popup menu.

Double click on "ThisWorkbook" in the "VBAProject" window.

Copy and paste all of the code (below) into the code window. The code window is usually on the right side of the VBA IDE and has two dropdown boxes at the top. They will probably contain the words (General) and (Declarations).

Close the VBA IDE and change something in column A.

Hope it helps.

Gary

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Row = 1 Then Exit Sub
If Target.Cells.Count <> 1 Then Exit Sub

If Target.Column = 1 Then
    If Target.Value <> Target.Offset(-1, 0).Value Then
        Target.Resize(, 23).Interior.ColorIndex = 4 'Change 4 to desired color
        Target.Font.Bold = True
    Else
        Target.Resize(, 23).Interior.ColorIndex = xlColorIndexNone
        Target.Font.Bold = False
    End If
End If

End Sub

 

[Lea82]

Hello Gary,

Thank you sooooo much for your quick reply!!

I tried to understand your coding - unfortunately I don't

I'm already back home now and the xls-file at work.
But I'll try it out first thing I get to office in the morning and then let you know again!!!

Can I attach a file to my post here? Just in case...

Thanks again and talk to you tomorrow

Ciao Lea

 

[Gary McMaster]

Can I attach a file to my post here? Just in case...

Files cannot be attached. Some people upload files to file sharing websites and then post a link to the file here. Others will exchange email addresses through private messages and share files via private email. If you click on the name of a person posting on this forum you will see an option "Send a private message to ..."

Gary

 

[Gary McMaster]

I replied to your private message but can't find any record of my reply. Did you get it?

Gary

 

[Lea82]

Hello

I am sorry, I just saw your post!
Yes, I send to you an email to the address you gave me the day I received your message..

Did you not get it? I also sent you the xls file...
Let me send it again. So sorry for the delay and so many thanks for your help!!!

Talk to you soon, Lea