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.