Change color in cell based on range of values, and change the color of same cell based on value in another range once that's entered.

nscaria00

New Member
Joined
Apr 21, 2023
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hi, I need help as I am totally new to VBA.
I have an excel document that's like a dashboard. I have Column 'N' for EO status'. User enters information in rows from A6 to E6 (same way for each rows). When someone enters infomartion, then Column 'N against that row needs to turn RED. Once Management signs off, which is in column 'F to M', then Column 'N' against that row needs to turn to Green.
Can you please help developing a code.
 

Attachments

  • Status Dashboard.PNG
    Status Dashboard.PNG
    39.1 KB · Views: 27

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
something like this:?
mr excel questions 25.xlsm
ABCDEFGHIJKLMNOP
1
2
3
4
5Data1Data2Data3Data4Data5NewData1NewData2NewData3NewData4NewData5NewData6NewData7
616131920191619171816151614TRUETRUE
71311121917FALSETRUE
8FALSEFALSE
9191617151418116FALSEFALSE
Sheet7
Cell Formulas
RangeFormula
O6:O9O6=COUNTA($A6:$M6)=COLUMNS($A6:$M6)
P6:P9P6=COUNTA($A6:$E6)=COLUMNS($A6:$E6)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
N6:N9Expression=COUNTA($A6:$M6)=COLUMNS($A6:$M6)textNO
N6:N9Expression=COUNTA($A6:$E6)=COLUMNS($A6:$E6)textNO
 
Upvote 0
Thank you for taking time to help!. I tried cells with Conditional formatting as you mentioned above.
It takes only one condition at a time and whatever condition stays on top, it will pull that one.
Sorry, I am just trying to learn excel and formulas. May be I selected wrong. I attach both screen shots.
a) I included both formulas , and b) the conditional formatting formula where I added the above formula.
 

Attachments

  • Picture1.png
    Picture1.png
    102.5 KB · Views: 22
  • Picture2.png
    Picture2.png
    101.2 KB · Views: 25
Upvote 0
in the conditional formatting rules you have options to go all the way through the rules for that cell or to stop when a condition is met.

additionally in columns O and P I have written the Formulas that are copied into the conditional formatting rules, and used to validate that I have it correct.
 
Upvote 0
I tried alot and your solution above is not working. I entered the formula you have given and arranged conditional formatting rules in sequence. But not working. See attached below. It's not turning to green even after entering all values.
 

Attachments

  • Capture.JPG
    Capture.JPG
    167.2 KB · Views: 16
Upvote 0
Did you build a formula that resolved to TRUE? I can assure you that if you do that then you will have the formula to put into the conditional formatting formula bar.
 
Upvote 0
Nope, Not working. Attaching the actual form. Can you take a look and correct if my formula is wrong.
Row 6_-> Evenif I have one name not entered--> showing as Green (should show as Red)
Row 7--> Only entered A7-E7 --> SHould show N7 as Red (SHowing as green).
Not sure why!.
 
Upvote 0
Trial form -Rev 2.xlsm
ABCDEFGHIJKLMN
2Year 2023
3EO #InitiatorDatePart #Change DescriptionCompletion StatusCompletion dateEO Status
4AccountingPurchasingCustomer ServiceQualityEngineering ProductionFinal Sign off
5PatMat LeahJohnDavidAnishAnil03/27/2023
623-02adam2023-04-286655545PatMatLeah DavidAnishAnil2023-04-28EO 23-02
723-03adam2023-04-281523541        EO 23-03
Engineeeing Order master file
Cell Formulas
RangeFormula
F6F6='23-02'!$D$44
G6G6='23-02'!$D$45
H6H6='23-02'!$D$46
I6I6='23-02'!$D$47
J6J6='23-02'!$D$48
K6K6='23-02'!$D$49
L6L6='23-02'!$F$52
M6M6='23-02'!$D$52
F7F7='23-03'!$D44
G7G7='23-03'!$D45
H7H7='23-03'!$D46
I7I7='23-03'!$D47
J7J7='23-03'!$D48
K7K7='23-03'!$D49
L7L7='23-03'!$F52
M7M7='23-03'!$D52
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L6Cell Valuecontains ""textNO
L6Cell Valuecontains ""textNO
L6Cell Valuecontains ""textNO
L6Cell Valuecontains ""textNO
L6Cell Valuecontains ""textNO
G6:G7Cell Value=$G$5textNO
N6Expression=COUNTA($A6:$M6)=COLUMNS($A6:$M6)textNO
N6Expression=COUNTA($A6:$E6)=COLUMNS($A6:$E6)textNO
N7Expression=COUNTA($A7:$M7)=COLUMNS($A7:$M7)textNO
N7Expression=COUNTA($A7:$E7)=COLUMNS($A7:$E7)textNO
 
Upvote 0
I thought you were going to upload a file? The xl2bb is great, but you reference other worksheets, it is causing extra work.
 
Upvote 0

Forum statistics

Threads
1,224,552
Messages
6,179,486
Members
452,917
Latest member
MrsMSalt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top