formatting colour based on different cells

[DKEXCEL]

Hi all,

I am creating a spreadsheet to track job progress. Each job has upto 8 different tasks.

For each task
I have 3 columns in a table named "Required", "Booked" and "complete"
Required will a yes/no from drop down box
booked and received will contain dates

I then have 8 columns in the table that I would like to indicate the status of each task by changing the fill coulour.
Task required - if no fill=grey
required but no booked date - red fill
required and booked date - yellow fill
completed date - green fill.

I have started using conditional formatting but I feel like there should be an easier way given the are 8 columns I would need to apply each condition to


Thank you in advance for your help

Dave

 

[My Aswer Is This]

I would like to know the below details in a effort to help you:
1.What is the name of the worksheet with the table.
2. What is the name of the Table
3. You said:
Required", "Booked" and "complete
Is it really complete or is it Complete
This is neede when writing Vba scripts
Now the fill colors.
Vba Green and Red are easily defined
But you will have to provide the exact RGB color to specify Grey
It needs to look like this:
RGB(34,46,65) or something like that.
You cannot say VbGrey

And then you mentioned 8 more columns
I need to know all the column Names
Be sure and capitalize exactly

And you said:
I then have 8 columns in the table that I would like to indicate the status of each task by changing the fill coulour.

And how do you plan to change the fill color?
For a script to run automatically you need to perform some specific action
Like if you enter r in that cell the cell color would change

So if you can answer these questions I may be able to help you

 

[DKEXCEL]

Thankyou so much for your response.

I am quite the novice user but very eager to start learning more around excel and access to create some tools to help streamline processes at work.

Would it work if I was to send you the spreadsheet file so you could see exactly what am talking about?

 

[My Aswer Is This]

Thankyou so much for your response.

I am quite the novice user but very eager to start learning more around excel and access to create some tools to help streamline processes at work.

Would it work if I was to send you the spreadsheet file so you could see exactly what am talking about?

I never open files posted to this forum.
And you answered none of my questions. So I see no way I can help you without the answers to my question.

 

[DKEXCEL]

Thankyou again for your reply.
I understand your reasoning, sorry i didnt even think about risks for you from opening unknow files. I have answered all of your questions below.
I have added another snip if the table as I have changed some of the fields (all info below matches the table)

Thankyou in advance for any help you can offer.

I would like to know the below details in a effort to help you:
1.What is the name of the worksheet with the table.
Worksheet name: "Database"

2. What is the name of the Table
Table name "Jobs"
3. You said:
Required", "Booked" and "complete
Is it really complete or is it Complete
-see below for exact details. This is the first of 8 tasks. I would assume if you can guide me for this first solution I will be able to copy and paste and then update corresponding names and cell locations?
G8 "DOORS" (Cell to change colour automatically based on data entered into required, order & received dates)
Z8 "DOORS REQUIRED" (drop down list "YES" & "NO" Options)
AF8 "DOORS ORDER DATE" (Date to be entered)
AG8 "DOORS DATE RECEIVED" (Date to be entered)


This is neede when writing Vba scripts
Now the fill colors.
Vba Green and Red are easily defined
But you will have to provide the exact RGB color to specify Grey
It needs to look like this:
RGB(34,46,65) or something like that.
You cannot say VbGrey

RGB (128,128,128)

And then you mentioned 8 more columns
I need to know all the column Names

There are 8 more tasks with similar input and output required as per below.
I assume I could use the solution for the task "DOORS" and duplicate and edit to match the tasks below?

H8 "HARDWARE" (Cell to change colour automatically based on data entered into required, order & received dates)
AH8 "HW REQUIRED" (drop down list "YES" & "NO" Options)
AI8 "HW ORDER DATE" (Date to be entered)
AJ8 "HW DATE RECEIVED" (Date to be entered)

I8 "BOARD" (Cell to change colour automatically based on data entered into required, order & received dates)
AK8 "BOARD REQUIRED" (drop down list "YES" & "NO" Options)
AL8 "BOARD ORDER DATE" (Date to be entered)
AM8 "BOARD DATE RECEIVED" (Date to be entered)

J8 "BT" (Cell to change colour automatically based on data entered into required, order & received dates)
AN8 "BT REQUIRED" (drop down list "YES" & "NO" Options)
AP8 "BT ORDER DATE" (Date to be entered)
AQ8 "BT DATE RECEIVED" (Date to be entered)

K8 "CM" (Cell to change colour automatically based on data entered into required, booked & complete dates)
AR8 "CM REQUIRED" (drop down list "YES" & "NO" Options)
AS8 "CM DATE BOOKED" (Date to be entered)
AT8 "CM DATE COMPLETE" (Date to be entered)

L8 "PRODUCTION DRAWINGS"
(Cell to change colour automatically based on data entered into production drawings complete field,
IF CELL A8 "JOB NAME" IS BLANK, CELL L8 IS DEFAULT TABLE COLOUR (IE NO CHANGE TO FILL)
If Cell A8 "JOB NAME" contains text then cell L8 = YELLOW.
If AU8 "PRODUCTION DRAWINGS COMPLETE" =YES then cell L8=GREEN
AU8 "PRODUCTION DRAWINGS COMPLETE" (drop down list "YES" & "NO" Options)

M8 "TRADES BOOKED" (Cell to change colour automatically based on required and booked data)
AV8 "TRADES REQUIRED" (drop down list "YES" & "NO" Options)
AW8 "REQ TRADES BOOKED" (drop down list "YES" & "NO" Options)

N8 "INSTALL BOOKED" (Cell to change colour automatically based on data yes/no and date entered
AX8 "INSTALL REQ" (drop down list "YES" & "NO" Options)
AY8 "INSTALL DATE" (Date to be entered)


Be sure and capitalize exactly

All capitalized for simplicity



And you said:
I then have 8 columns in the table that I would like to indicate the status of each task by changing the fill coulour.

And how do you plan to change the fill color?
For a script to run automatically you need to perform some specific action
Like if you enter r in that cell the cell color would change

Ok the plan is as follows...
A new job is created by entering client and job details in relevant fields.
Once a job name is entered into cell AB "JOB NAME" all 'status' cells G8 - N8 will turn red with the exception of L8 "PRODUCTION DRAWINGS" which will turn yellow as this task is required for every job.

For each task the following will occur
The 'status' cell will then turn from red to GREY if a "NO" is entered into the '(task) req' field indicating the task is not required for the job - (status is greyed out)
The 'status cell' will then turn from red to yellow once a date has been entered into the 'ordered' or 'booked' field for each task as appropriate - indicating the task is underway but not complete
The 'status cell' will then turn green once a date has been entered into the 'received' or 'complete' field for the relevant task.



So if you can answer these questions I may be able to help you

 

[My Aswer Is This]

To have a script run automatically we need to know when you enter what value in what column.
Will cause the script to run.
For example in you enter "Alpha" in Range("A4") the script starts to run and looks to see what is in B4 and then looks to see what is in H4 and on and on and according to what it finds do this in in Range("M4")

And we are dealing with a great number of if this or that's

So if you can give me one example of what you might want to enter in what column to activate the script and what all the if's may be. Tell me column letters like A4 and F4 and H4

And I will see what I can do. But this may be beyond my Knowledge base.
I can see you have a lot of if that or if this. But I appreciate your explanation in last posting

But since we are dealing with a lot of column if this and if that it would be easier to say if cell in column A or column G this is entered.
And be sure and say when I enter this in column A for example it will cause the script to automatically run.

 

[DKEXCEL]

To have a script run automatically we need to know when you enter what value in what column.
Will cause the script to run.
For example in you enter "Alpha" in Range("A4") the script starts to run and looks to see what is in B4 and then looks to see what is in H4 and on and on and according to what it finds do this in in Range("M4")

And we are dealing with a great number of if this or that's

So if you can give me one example of what you might want to enter in what column to activate the script and what all the if's may be. Tell me column letters like A4 and F4 and H4

And I will see what I can do. But this may be beyond my Knowledge base.
I can see you have a lot of if that or if this. But I appreciate your explanation in last posting

But since we are dealing with a lot of column if this and if that it would be easier to say if cell in column A or column G this is entered.
And be sure and say when I enter this in column A for example it will cause the script to automatically run.

Ok thanks. Like I said if you could help with making it run for one task Im reasonably confident I can adapt it to work on the other 7 tasks.
The plan is..

FOR TASK DOORS
(This is all within the table "JOBS")

A8 is first column of the table "JOBS". column title "NAME"
I enter a text into cell a A9
Cell G9 turns PURPLE (RGB 214,48,197)


Z8 column name "DOORS REQUIRED"
As part of entering the job details I select if doors are required or not using cell Z9.

IF I select "NO" from the drop down list in cell Z9 cell G9 will fill GREY. (If NO is entered at this stage there will be no further changes to the fill of cell G9. it will turn GREY and remain grey if Z9 = NO
IF I select "YES" from the drop down list cell G9 will remain RED.


AF9 column name "DOORS ORDER DATE"
once the doors have been ordered cell G9 will turn from red to yellow indicating we are now waiting on the doors.

IF I enter a date into cell AF9 cell G9 turns YELLOW.


AG8 column name "DOORS DATE REVEIVED"
once I enter a date date in cell AG9 cell G9 will turn Green.
There will be no further changes to cell G9 regardless of any other data entered into the table.


IN SUMMARY

text entered into A9 > cell G9 PURPLE ------"NO" entered in cell Z9 > cell G9 GREY (NO FURTHER CHANGES TO G9 unless Z9 is changed to YES. otherwise it will remain grey and script stops.)
|
|
YES in Z9 > cell G9 RED ---- Date entered to AF9>cell G9 YELLOW ----Date into AG9>cell G9 GREEN


I hope this helps?

 

[My Aswer Is This]

Here is a example.
To perform a script automatically when you enter a value in a cell we need to use a script like this.
This script starts work in Row 9 see part of script that says >8

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter a value in the proper column the script runs

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/18/2021  7:27:23 AM  EDT

If Target.Row > 8 Then
Dim ans As Long
ans = Target.Row

Select Case True
Case Target.Column = 1
    If Target.Value = "Alpha" Then Cells(ans, "I").Interior.Color = RGB(214, 48, 197)


Case Target.Column = 9
    If Target.Value = "No" Then Cells(ans, "Z").Interior.Color = RGB(174, 170, 170)
    If Target.Value = "Yes" Then Cells(ans, "Z").Interior.Color = vbRed

Case Target.Column = 33
If IsDate(Target.Value) Then Cells(ans, "G").Interior.Color = vbGreen

End Select

End If
End Sub

 

[My Aswer Is This]

In previous post:
Case Target.Column = 9

Means if you enter a value in column 9

 

[My Aswer Is This]

Try this instead. I added a line of code for error catching

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/18/2021  8:39:28 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row > 8 Then
Dim ans As Long
ans = Target.Row

Select Case True
Case Target.Column = 1
    If Target.Value = "Alpha" Then Cells(ans, "I").Interior.Color = RGB(214, 48, 197)


Case Target.Column = 9
    If Target.Value = "No" Then Cells(ans, "Z").Interior.Color = RGB(174, 170, 170)
    If Target.Value = "Yes" Then Cells(ans, "Z").Interior.Color = vbRed

Case Target.Column = 33
If IsDate(Target.Value) Then Cells(ans, "G").Interior.Color = vbGreen

End Select

End If
End Sub