Change row color without conditional formatting

Rurkz · Feb 21st 2021

I have 4 headers representing colors

Date Paid = Green
Date Delivered = Red
Date Collected = Yellow
Overweight Fee = Blue

My data starts at row 3

Date Paid starts at "F3"

Date Delivered starts at "G3"

Date Collected starts at "H3"

Overweight Fee starts at "L3"

To make it simple, if any type of value is inputted into these columns I just want the row to change color to the related headers and the rows to change colors again if I input values in other columns.

**royUK** · Feb 22nd 2021

Why can't you use Conditional Formatting?

**Carim** · Feb 22nd 2021

Hello and Welcome to the Forum

Attached is your test file

Hope this will help

**pike** · Feb 22nd 2021

Hi,

Maybe Worksheet Change event

Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Union(Range("F:H"), Range("L:L")), Target) Is Nothing And Target.Row > 3 Then
        Select Case Target.Column
        Case 6
            Target.Interior.Color = vbGreen
        Case 7
            Target.Interior.Color = vbRed
        Case 8
            Target.Interior.Color = vbYellow
        Case 12
            Target.Interior.Color = vbCyan
        End Select
        If Target.Value = vbNullString Then Target.Interior.Pattern = xlNone
     End If
     Application.EnableEvents = True
End Sub

Display More

Rurkz · Feb 22nd 2021

hey royk

the issue i was running into with conditional formatting was between Date Paid and Date Collected. So if the row changed green when paid, then yellow when collected, that would work perfectly but if it was vice versa the row will stay yellow and not turn green.

**royUK** · Feb 22nd 2021

Maybe your Conditional Formatting formulas were wrong orb possibly the sequence in which they ran.

Rurkz · Feb 22nd 2021

i believe its the order of my rules, because of that i cant override it if you get what i mean.

Rurkz · Feb 22nd 2021

Quote from Carim

Hello and Welcome to the Forum

Attached is your test file

Hope this will help

Display More

hey Carim I took a look at your file, I apologize, looks great, I forgot to mention I wanted to highlight the entire row, A3 to L3 etc... So if I input a value in Date delivered, the row is highlighted red. Then if I enter a value in date collected, the row will change to yellow, and when its finally paid, it will change to green. Sometimes it might be paid before collected, that means the row can change from green to yellow.......im really trying to have it setup so the colors can be overridden no matter what order a value is inputted if you get what I mean.....Appreciate it

**royUK** · Feb 22nd 2021

Can you attach a small example workbook with your CF in.

**Carim** · Feb 22nd 2021

Hello again,

Not a big deal ... only a tiny modification ...

Hope this will help

**pike** · Feb 22nd 2021

hello,

Can you attach a small example workbook with your CF in ?

**Carim** · Feb 23rd 2021

Hello,

Once you have tested the macro ... feel free to share your comments ...

Rurkz · Feb 23rd 2021

hey didnt see the notification reply until i checked manually.......carim you awesome!!!!! this exactly what i needed!!!!

it seems to work in your file, here is my file, but i didnt get it to work in mine

**Carim** · Feb 23rd 2021

Thanks for your file

Do you need the macro to operate in some specific worksheets ... or would you like to have it for all your worksheets ...?

**Carim** · Feb 23rd 2021

Well ... the file you are currently using was designed by Jon Wittwer for Vertex ...

And you cannot have several similar events such as Worksheet_Change() within the same sheet ...

Rurkz · Feb 24th 2021

im a complete noob, so i have no idea what worksheet_change() is lol. I did however have someone create a macro for me if thats wha you are talking about.

and yes if i can have it operate in all worksheets that would be awesome

**Carim** · Feb 24th 2021

Quote from Rurkz

im a complete noob, so i have no idea what worksheet_change() is lol. I did however have someone create a macro for me if thats what you are talking about ....

Could you friend next to you who has already designed the first macro help you out ... ?

Rurkz · Feb 24th 2021

Quote from Carim

Could you friend next to you who has already designed the first macro help you out ... ?

I'm not sure if he/she could, i posted the question on mrexcel around a month..and got a response for that macro...then I asked about changing the row color and the moderator told me to start a new post...I did and never got help for it. Believe it or not, you are the only person who has helped me with this situation....also forgot to mention wittwer from vertex, it was a free expense sheet i download around a year ago and modified it.

**royUK** · Feb 24th 2021

This will explain where the different sorts of code need to be

Where to put your Excel VBA Code

**Carim** · Feb 24th 2021

Quote from Rurkz

I'm not sure if he/she could, i posted the question on mrexcel around a month..and got a response for that macro...then I asked about changing the row color and the moderator told me to start a new post...I did and never got help for it.
Believe it or not, you are the only person who has helped me with this situation....also forgot to mention wittwer from vertex, it was a free expense sheet i download around a year ago and modified it.

Whenever I have a moment ... will drill down the workbook structure ... and try to find a complete workable solution

Change row color without conditional formatting

Participate now!

Share

Users Viewing This Thread