Question description:
This user has given permission to use the problem statement for this
blog.
I need cell B2 Green if cells G2:Q2 are empty. If cells G2:Q2 have any text in any cell then i need B2 to be red.
Solved by C. W. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/08/2018 - 02:04
Hi there!
User
08/08/2018 - 02:04
hey!
Excelchat Expert
08/08/2018 - 02:04
I believe a conditional formatting formula will be able to solve this for you. Do you have a particular file you're working with? Are you able to upload it here?
User
08/08/2018 - 02:05
i do have a file but i am unable to upload it
Excelchat Expert
08/08/2018 - 02:06
No problem. I will code the conditional formatting formula and then you can add it to your file. Are you familiar with conditional formatting?
User
08/08/2018 - 02:06
yes, however i'm using google sheets. I can use excel if need be though.
Excelchat Expert
08/08/2018 - 02:07
Oh no Google Sheets is just fine! I will code it directly in here then ------>
User
08/08/2018 - 02:08
i have an error on the browser so i cant see
Excelchat Expert
08/08/2018 - 02:08
No problem.
User
08/08/2018 - 02:08
i might have to refresh but i dont want to lose the session
Excelchat Expert
08/08/2018 - 02:08
Ok first can you highlight cell B2 green
User
08/08/2018 - 02:08
ok
Excelchat Expert
08/08/2018 - 02:08
The formula will make it red if the conditions are met
Excelchat Expert
08/08/2018 - 02:08
( if cells G2:Q2 have any text)
Excelchat Expert
08/08/2018 - 02:09
One sec and I will get this for you.
Excelchat Expert
08/08/2018 - 02:09
Yeah don't bother refreshing the browser it's all good
User
08/08/2018 - 02:09
yea it seems simple i had something like =IF(G2:Q2)=""
User
08/08/2018 - 02:09
thats about all i had
Excelchat Expert
08/08/2018 - 02:10
That is close for sure... I am using SUMPRODUCT as well.
Excelchat Expert
08/08/2018 - 02:11
Ok go to cell B2
Excelchat Expert
08/08/2018 - 02:11
conditional formatting
User
08/08/2018 - 02:12
k
Excelchat Expert
08/08/2018 - 02:12
Format cells IF and then choose custom formula
Excelchat Expert
08/08/2018 - 02:12
This is the custom formula:
Excelchat Expert
08/08/2018 - 02:12
=SUMPRODUCT(--(G2:Q2<>""))=0
Excelchat Expert
08/08/2018 - 02:12
then choose a formatting style (red)
Excelchat Expert
08/08/2018 - 02:13
That should do it! Let me know if it works on your end. No rush ~
User
08/08/2018 - 02:14
that worked perfectly!! Now lets say i need all cells in the B column to work like that for example B3 with the conditional formatting to G3:Q3
User
08/08/2018 - 02:14
do i have to make the conidtional for each b cell in that column
Excelchat Expert
08/08/2018 - 02:15
Nope! Drag down cell B2 (like pretend it has a formula in it that you're dragging down)
Excelchat Expert
08/08/2018 - 02:15
It will dynamically update to the proper row
User
08/08/2018 - 02:15
YES!! perfect
Excelchat Expert
08/08/2018 - 02:15
Yay!
Excelchat Expert
08/08/2018 - 02:16
My pleasure to help! Please feel free to reach out with a new session if you need more help and we will be happy to assist.
Excelchat Expert
08/08/2018 - 02:16
All the very best ~
User
08/08/2018 - 02:16
that was awesome! tyvm!!!
Excelchat Expert
08/08/2018 - 02:16
:)
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.