All solutions CONDITIONAL FORMATTINGIF Expert Solution – How to Use Conditional Formatting With IF Statement

Excel - How to Use Conditional Formatting With IF Statement - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Yes hello Im looking for help on conditional formatting I currently have a conditional format for D9>D32 and so on from D9:S20. I need to add an if statement that references cell D4 if the first four characters are 1358 for the rule to apply can you please assist?
Solved by Z. U. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 21/08/2017 - 01:14
yeah so the rule applies for each column exclusively so for the rule to apply to D9>D32 it needs to reference D4
Excelchat Expert 21/08/2017 - 01:14
Hello, I understand that you need help win building a conditional formatting that checks if the first four characters are 1358, right?
User 21/08/2017 - 01:14
yes exactly
Excelchat Expert 21/08/2017 - 01:14
Will you be able to give a sample document that we can work on?
User 21/08/2017 - 01:14
but each column is exclsuive so, E will need to reference E4
User 21/08/2017 - 01:15
i cant sorry i cant share my companies materila
User 21/08/2017 - 01:15
Ill send you my current formula though
Excelchat Expert 21/08/2017 - 01:15
I'm not really asking for the actual data. Just a sample data that we can work on.
Excelchat Expert 21/08/2017 - 01:15
That way, we can test if the CF i'll be creating fits your requirement.
User 21/08/2017 - 01:15
I can't upload anything though
User 21/08/2017 - 01:15
=IF(LEFT(D$4,3)="'13","D9>D32","")
Excelchat Expert 21/08/2017 - 01:16
You don't have to upload anything. Just Try to type a few sample data.
User 21/08/2017 - 01:16
this is what Ive tried to use but it doesnt work
User 21/08/2017 - 01:16
sorry this one
User 21/08/2017 - 01:16
=IF(LEFT(D$4,4)="'135","D9>D32","")
Excelchat Expert 21/08/2017 - 01:16
Yes because conditional formatting will only accept TRUE or FALSE.
User 21/08/2017 - 01:16
can you explain?
Excelchat Expert 21/08/2017 - 01:17
Conditional formatting will check if your statement is TRUE or FALSE. If it is true then it will highlight the cell.
Excelchat Expert 21/08/2017 - 01:17
Otherwise, it won't do anything.
User 21/08/2017 - 01:17
okay so how do i fix it?
Excelchat Expert 21/08/2017 - 01:17
For example, your formula might be:
Excelchat Expert 21/08/2017 - 01:17
=left(d4,4)="1358"
Excelchat Expert 21/08/2017 - 01:18
But I can't tell you exactly what the formula should be because I have no clue how your data looks like.
User 21/08/2017 - 01:18
so from D9:D20 i have values
User 21/08/2017 - 01:18
then from D32:D43 i have calues
User 21/08/2017 - 01:18
values*
Excelchat Expert 21/08/2017 - 01:18
Do you see the document preview to the right?
User 21/08/2017 - 01:19
and i need it to highlight red if D9>D32
User 21/08/2017 - 01:19
and so on for each cell but only if first four characters of D4=1358
User 21/08/2017 - 01:19
no its blocked
User 21/08/2017 - 01:19
does that make sense to you though?
Excelchat Expert 21/08/2017 - 01:19
So it compares D9 with D32, D10 with D33 and so on?
User 21/08/2017 - 01:20
yeah exactly
Excelchat Expert 21/08/2017 - 01:20
And what's the role of D4?
Excelchat Expert 21/08/2017 - 01:20
Does D4 change?
User 21/08/2017 - 01:21
yes so only if it has 1358 as the first four characters
Excelchat Expert 21/08/2017 - 01:21
I mean is it always checking D4 or does it move downwards like D9 to d10 and d32 to d33?
User 21/08/2017 - 01:21
thats the only time i want the conditional formatting to apply to D9>D32, and so on
User 21/08/2017 - 01:21
no it doesnt
User 21/08/2017 - 01:21
d4 is always the same
Excelchat Expert 21/08/2017 - 01:22
=and(left($D$4,4)="1358",D9>D32)
User 21/08/2017 - 01:23
that make sense?
Excelchat Expert 21/08/2017 - 01:23
Entering Explanation Phase
Excelchat Expert 21/08/2017 - 01:23
We'll use the AND() function to generate a TRUE or FALSE value for the custom conditional formatting formula.
Excelchat Expert 21/08/2017 - 01:24
I just tried that formula in my conditional formatting and it works based on how I understood your problem.
User 21/08/2017 - 01:24
ok it works for D column
User 21/08/2017 - 01:24
now i need it for columns D:S
User 21/08/2017 - 01:25
but so like column E references E4
User 21/08/2017 - 01:25
F references F4 and so on
User 21/08/2017 - 01:25
that make sense?
Excelchat Expert 21/08/2017 - 01:25
If it is in E, it uses E4 and then compares E9 with E32?
User 21/08/2017 - 01:26
exactly
Excelchat Expert 21/08/2017 - 01:26
=and(left(D$4,4)="1358",D9>D32)
User 21/08/2017 - 01:26
and then use the format painter?
Excelchat Expert 21/08/2017 - 01:26
Use that and make sure you encompass D9:S20 for the range of the conditional formatting or use the format painter.
Excelchat Expert 21/08/2017 - 01:27
Entering Discussion Phase
User 21/08/2017 - 01:27
hmm doesnt seem to be working
User 21/08/2017 - 01:27
works for column D
User 21/08/2017 - 01:27
but not the others
Excelchat Expert 21/08/2017 - 01:27
I just used the same formula and it works in the document preview.
Excelchat Expert 21/08/2017 - 01:28
Unfortunately, you don't have access to it and I don't see what you are doing.
User 21/08/2017 - 01:30
=AND(LEFT(D$4,4)="1358",D9>D32)
User 21/08/2017 - 01:30
just tried that
User 21/08/2017 - 01:30
for some reason its not working
Excelchat Expert 21/08/2017 - 01:30
I can't really tell you what you are doing wrong because I don't see what you've done so far. :(
Excelchat Expert 21/08/2017 - 01:30
I guarantee you that formula works though.
User 21/08/2017 - 01:31
ok ill play with it a bit
User 21/08/2017 - 01:31
thanks for your help
Excelchat Expert 21/08/2017 - 01:32
Can you access Drive.google.com?
Excelchat Expert 21/08/2017 - 01:32
https://drive.google.com/file/d/0B1j34SU3SZazTjJsalFFZUxSQnc/view?usp=sharing
Excelchat Expert 21/08/2017 - 01:33
If you could find a way to download that file, I've applied the formula there and you will see that it works.
Excelchat Expert 21/08/2017 - 01:33
Please save that file.
Excelchat Expert 21/08/2017 - 01:33
We have 15 seconds to go. Please make sure you copy the path.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc