Conditional Formatting

[ChristineC]

Hello I am trying to create some conditional formatting rules:

Column J has either G, T or V within it.
Column K (lets call this Purchase date)
Column V (term date)

There are other columns I want to do but if i can get help with this I think I can manage to figure out the rest.

So what I'd like to use conditional formatting for is I would like data in Column K & V to be formatted based off the data in column J (so if J3 has G I would like column K3 & V3 to have formatting) I'm trying to grey out fields that do not apply to the category type... I've tried on own and give up...

 

[etaf]

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
K2:K100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$J2="G"

Format [Number, Font, Border, Fill] format as grey
choose the format you would like to apply when the condition is true
OK >> OK

Repeat the above - BUT select
V2:V100 - Change, reduce or extend the rows to meet your data range of rows
=$J2="G"

You may be able to select K and V using control key

just tested and yes you can select the K AND V range
using
Apply to $K$2:$K$100,$V$2:$V$100
or as i say select K and then V

Book2
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3
4
5
6
7
8		g
9
10
11
12		g
13
14
15
16		g
17
18
19
20
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:K30,V2:V30	Expression	=$J2="G"	text	NO

Apply to $K$2:$K$100,$V$2:$V$100

 

[dreid1011]

How about this:

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Code	Purchase Date											Term Date
2	V	6/28/2024											10/28/2024
3	G	6/29/2024											10/29/2024
4	T	6/30/2024											10/30/2024
5	T	7/1/2024											10/31/2024
6	V	7/2/2024											11/1/2024
7	G	7/3/2024											11/2/2024
8	G	7/4/2024											11/3/2024
9	T	7/5/2024											11/4/2024
10	T	7/6/2024											11/5/2024
11	T	7/7/2024											11/6/2024
12	T	7/8/2024											11/7/2024
13	G	7/9/2024											11/8/2024
14	V	7/10/2024											11/9/2024
15	V	7/11/2024											11/10/2024
16	T	7/12/2024											11/11/2024
17	T	7/13/2024											11/12/2024
18	V	7/14/2024											11/13/2024
19	V	7/15/2024											11/14/2024
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V2:V19	Expression	=$J2="G"	text	NO
K2:K19	Expression	=$J2="G"	text	NO

 

[ChristineC]

ok ... I have followed the suggested formulas. I don't know why but fields that do not meet the criteria are highlighting.. I cleared the formatting from the entire worksheet to be certain it wasn't something else I had tried.

 

[dreid1011]

If your "applies to" range starts in K3, then your formula should also start as row 3, not row 5. Also, CF is smart and auto increments formula references, so you should change $J5:J102 to $J3. You don't need to use the range. CF will automatically update $J3 to $J4, $J5, $J6 etc.

So it should be

$J3="G"

And if you're going to use the entire column for V:V, then that formula should be

$J1="G"

Your CF formulas should always start with the same row as your applies to range or you will get skewed results. Whole column references start at row 1.

 

[ChristineC]

omg... I triple checked that I had formulas right... thank you all.