Excel - COLUMN Function Problem - Expert Solution

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

Hi, I am trying to create a formula where column J turns red when anything is entered into column A, but then column J goes back to no color when anything is entered into it
Solved by O. H. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 26/08/2018 - 07:08
Hi
Excelchat Expert 26/08/2018 - 07:08
Welcome to Got it Pro
User 26/08/2018 - 07:08
Hi
Excelchat Expert 26/08/2018 - 07:08
The ask that you have can be done using conditional formatting
User 26/08/2018 - 07:09
I am able to do basics but am stumped by this one
Excelchat Expert 26/08/2018 - 07:10
See J1
Excelchat Expert 26/08/2018 - 07:10
Try entering value in column A to see the effect
Excelchat Expert 26/08/2018 - 07:11
Remove the value and see
User 26/08/2018 - 07:12
J still stays red after entering a value
Excelchat Expert 26/08/2018 - 07:12
You want it to become no color?
User 26/08/2018 - 07:12
yes
Excelchat Expert 26/08/2018 - 07:15
See now
Excelchat Expert 26/08/2018 - 07:15
Try
User 26/08/2018 - 07:16
YESSSSSSS...:)
Excelchat Expert 26/08/2018 - 07:16
Does it help?
Excelchat Expert 26/08/2018 - 07:16
Great
Excelchat Expert 26/08/2018 - 07:16
Please do give good rating and reviews when the session ends
Excelchat Expert 26/08/2018 - 07:16
You can download this file for your reference
Excelchat Expert 26/08/2018 - 07:16
Let me know if you need anything else
User 26/08/2018 - 07:17
wait, I still didn't see how you did it
Excelchat Expert 26/08/2018 - 07:17
I did it for one cell in cell J1
Excelchat Expert 26/08/2018 - 07:17
and then used format painter for other cells below it
Excelchat Expert 26/08/2018 - 07:17
When you click on J1 you can see the conditional formatting clauses i have sued on the right
Excelchat Expert 26/08/2018 - 07:17
Do you see?
Excelchat Expert 26/08/2018 - 07:18
=AND(A1<>"",J1="") then red
Excelchat Expert 26/08/2018 - 07:18
If cell J1 is not empty then no clor
Excelchat Expert 26/08/2018 - 07:18
Let me know if you understand it
Excelchat Expert 26/08/2018 - 07:19
Do you understand?
Excelchat Expert 26/08/2018 - 07:19
Please let me know
User 26/08/2018 - 07:19
please wait, I am trying it
Excelchat Expert 26/08/2018 - 07:20
Sure
Excelchat Expert 26/08/2018 - 07:20
You can also download this file for your reference
Excelchat Expert 26/08/2018 - 07:21
Hoping to get good rating and reviews from you
User 26/08/2018 - 07:23
when I enter into A9 it turns J16 red ???
Excelchat Expert 26/08/2018 - 07:24
No
Excelchat Expert 26/08/2018 - 07:24
Make formula for cell J1 and put formula for A1
User 26/08/2018 - 07:24
yes it does
User 26/08/2018 - 07:24
ok
Excelchat Expert 26/08/2018 - 07:24
Where it says apply to cell there enter just J1
Excelchat Expert 26/08/2018 - 07:24
in formula use custom formula and enter the formula i gave you
Excelchat Expert 26/08/2018 - 07:25
and other condition use when cell j1 is not empty then no color
User 26/08/2018 - 07:26
Im trying, not working
Excelchat Expert 26/08/2018 - 07:27
See now
Excelchat Expert 26/08/2018 - 07:27
i have removed rules for other cells
Excelchat Expert 26/08/2018 - 07:27
now see only for J1
Excelchat Expert 26/08/2018 - 07:27
Do you see conditional formatting window on spreadsheet?
User 26/08/2018 - 07:28
no
Excelchat Expert 26/08/2018 - 07:28
When you clcik on cell J1
Excelchat Expert 26/08/2018 - 07:28
Just click on J1
Excelchat Expert 26/08/2018 - 07:28
Do you see now on right?
Excelchat Expert 26/08/2018 - 07:28
conditional formatting ?
Excelchat Expert 26/08/2018 - 07:28
rules that i have applied
Excelchat Expert 26/08/2018 - 07:28
tell me
User 26/08/2018 - 07:28
it is blank
Excelchat Expert 26/08/2018 - 07:29
click on J1 and then click on format on top and select conditional formatting
Excelchat Expert 26/08/2018 - 07:29
now do you see the rules?
Excelchat Expert 26/08/2018 - 07:30
Follow this on the spreadsheet on right
Excelchat Expert 26/08/2018 - 07:30
Tell me
Excelchat Expert 26/08/2018 - 07:30
Please tell me
User 26/08/2018 - 07:31
you need to slow down
Excelchat Expert 26/08/2018 - 07:31
ok
User 26/08/2018 - 07:33
OK.. did it, but when i enter text into column A it turns red 7 rows below in column J
Excelchat Expert 26/08/2018 - 07:33
Which cell are you entering the value?
User 26/08/2018 - 07:34
Column J
Excelchat Expert 26/08/2018 - 07:34
and on which cell have you applied the conditional formatting
Excelchat Expert 26/08/2018 - 07:34
In apply to range have you seelcted cell J1?
User 26/08/2018 - 07:34
Entering Value in A
Excelchat Expert 26/08/2018 - 07:34
Enter value in A1
Excelchat Expert 26/08/2018 - 07:34
apply to range use cell J1
User 26/08/2018 - 07:34
applied formatting to J
Excelchat Expert 26/08/2018 - 07:34
Not to J
Excelchat Expert 26/08/2018 - 07:34
apply to J1
Excelchat Expert 26/08/2018 - 07:35
remove formatting from other cells
User 26/08/2018 - 07:35
my chart starts at row 9
Excelchat Expert 26/08/2018 - 07:35
you have previously applied some conditional formatting and have not removed them that is why it is appearing for cell below
Excelchat Expert 26/08/2018 - 07:35
just do on new sheet or excel
Excelchat Expert 26/08/2018 - 07:35
where does chart come now?
User 26/08/2018 - 07:36
Ahhhh... It works now
Excelchat Expert 26/08/2018 - 07:36
cool
Excelchat Expert 26/08/2018 - 07:36
Thanks
User 26/08/2018 - 07:36
I was applying it to the entire column of J
Excelchat Expert 26/08/2018 - 07:36
Yeah that is why
Excelchat Expert 26/08/2018 - 07:37
Please do give good rating and reviews when you end the session
Excelchat Expert 26/08/2018 - 07:37
It would really help a lot
User 26/08/2018 - 07:37
Thank you very much for yur patience. I am a 53 year old construction worker with grade 6 education... teaching myself computer
User 26/08/2018 - 07:38
where do i leave review?
Excelchat Expert 26/08/2018 - 07:38
When you end the session you can leave review
Excelchat Expert 26/08/2018 - 07:38
You can see a button to end session on your screen

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