conditional formatting

Z

zoinc

New Member
Joined
Jun 6, 2017
Messages
3
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]purchase 1[/TD]
[TD]purchase 2[/TD]
[TD]purchase 3[/TD]
[TD]Amount[/TD]
[TD]customer since[/TD]
[TD]years membership[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Abricht[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]0[/TD]
[TD][TABLE="width: 185"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]$291[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]6/7/1994[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]23 years[/TD]
[/TR]
[TR]
[TD]Annijs[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7

[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$495[/TD]
[TD] 9/9/1986[/TD]
[TD="align: right"]30 years[/TD]
[/TR]
[TR]
[TD]Bingen[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]$420[/TD]
[TD="align: right"]2/14/1983[/TD]
[TD="align: right"]24 years[/TD]
[/TR]
[TR]
[TD]Callens[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]$280[/TD]
[TD="align: right"]12/14/1997[/TD]
[TD="align: right"]19 years[/TD]
[/TR]
</tbody>[/TABLE]

Really sorry for my Table, I'll make something much better next time just need some more practice....
I simply can't manage to use conditional formatting just with one column using another has a reference

In column A. Mark the names of the customers with a light green fill when they are a member for 15 years or more.
Mark the names of the customers with a light yellow fill when they are less than 15 years a member.

Can someone give me a hand with this? I'm kind of desperate...thank you
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
After selecting the A column
CF for green =and($G2<>"",(left($G2,2)+0)>=15)
CF for yellow =and($G2<>"",(left($G2,2)+0)<15)
 
Upvote 0
zoinc said:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]purchase 1[/TD]
[TD]purchase 2[/TD]
[TD]purchase 3[/TD]
[TD]Amount[/TD]
[TD]customer since[/TD]
[TD]years membership[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD]Abricht[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD][TABLE="width: 185"]
<tbody>[TR]
[TD="align: right"]$291[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="align: right"]6/7/1994[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]23 years[/TD]
[/TR]
[TR]
[TD]Annijs[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$495[/TD]
[TD] 9/9/1986[/TD]
[TD="align: right"]30 years[/TD]
[/TR]
[TR]
[TD]Bingen[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]$420[/TD]
[TD="align: right"]2/14/1983[/TD]
[TD="align: right"]24 years[/TD]
[/TR]
[TR]
[TD]Callens[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]$280[/TD]
[TD="align: right"]12/14/1997[/TD]
[TD="align: right"]19 years[/TD]
[/TR]
</tbody>[/TABLE]

Really sorry for my Table, I'll make something much better next time just need some more practice....
I simply can't manage to use conditional formatting just with one column using another has a reference

In column A. Mark the names of the customers with a light green fill when they are a member for 15 years or more.
Mark the names of the customers with a light yellow fill when they are less than 15 years a member.

Can someone give me a hand with this? I'm kind of desperate...thank you
Click to expand...

highlight the names, go to Conditional Formatting -> New Rule -> Use a formula

=DATEDIF($F2,TODAY(),"Y")>=15
 
Upvote 0
CF

=SUBSTITUTE(VLOOKUP(A2,$A$2:$G$5,7,FALSE),"years","")*1<15 applies =$A$2:$A$5 yellow

=SUBSTITUTE(VLOOKUP(A2,$A$2:$G$5,7,FALSE),"years","")*1>=15 applies =$A$2:$A$5 green
 
Upvote 0
zoinc said:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]purchase 1[/TD]
[TD]purchase 2[/TD]
[TD]purchase 3[/TD]
[TD]Amount[/TD]
[TD]customer since[/TD]
[TD]years membership[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD]Abricht[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD][TABLE="width: 185"]
<tbody>[TR]
[TD="align: right"]$291[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="align: right"]6/7/1994[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]23 years[/TD]
[/TR]
[TR]
[TD]Annijs[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$495[/TD]
[TD] 9/9/1986[/TD]
[TD="align: right"]30 years[/TD]
[/TR]
[TR]
[TD]Bingen[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]$420[/TD]
[TD="align: right"]2/14/1983[/TD]
[TD="align: right"]24 years[/TD]
[/TR]
[TR]
[TD]Callens[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]$280[/TD]
[TD="align: right"]12/14/1997[/TD]
[TD="align: right"]19 years[/TD]
[/TR]
</tbody>[/TABLE]

Really sorry for my Table, I'll make something much better next time just need some more practice....
I simply can't manage to use conditional formatting just with one column using another has a reference

In column A. Mark the names of the customers with a light green fill when they are a member for 15 years or more.
Mark the names of the customers with a light yellow fill when they are less than 15 years a member.

Can someone give me a hand with this? I'm kind of desperate...thank you
Click to expand...

1.) select A2 > open Conditional Formatting > Use Formula > =$G2<15 > apply a format "color fills etc."
2.) select A2 > open Conditional Formatting > Use Formula > =$G2>=15 > apply a format "color fills etc."

use format painter to copy down.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Conditional Formatting Based on Multiple Columns in other Tabs
Replies
1
Views
707
covareo
C
L
  • Question Question
conditional formatting - like reverse lookup
Replies
3
Views
826
Fluff
Fluff
B
  • Question Question
Need help to conditionally format a cell in one cell of a table based on the values of two other tables
Replies
3
Views
943
DanteAmor
DanteAmor
C
  • Question Question
Suggest a pricing matrix for Adult/Concession/Junior ‘Flexi Season Ticket’.
Replies
2
Views
527
Chuky
C
M
  • Question Question
Conditional Formatting On Multiple Ranges
Replies
3
Views
756
Fluff
Fluff

Forum statistics

Threads
1,224,749
Messages
6,180,727
Members
452,995
Latest member
isldboy

Share this page

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