Go Back   Tech Support Forum > Microsoft Support > Microsoft Office support

User Tag List

Conditional formatting

This is a discussion on Conditional formatting within the Microsoft Office support forums, part of the Tech Support Forum category. OK, I have pondered this for two days; it so simple I'm ready to scream that I can't get it.


Reply
 
Thread Tools Search this Thread
Old 01-28-2020, 06:07 PM   #1
Registered Member
 
Join Date: Nov 2011
Location: Florida usa
Posts: 14
OS: windows 10 pro



OK, I have pondered this for two days; it so simple I'm ready to scream that I can't get it. I have Sheet 1,column A, company name. In Sheet 2, Column A, I want to enter company name. If it is not in Sheet 1, column a (a table, as is sheet 2) I want the cell to turn red. So simple. Why can't I figure it out?
cliffmid is offline   Reply With Quote
Sponsored Links
Advertisement
 
Old 01-28-2020, 09:57 PM   #2
Registered Member
 
Join Date: Aug 2007
Posts: 4
OS: Win 7, Win 8.1



I assume you are using Excel. This works in Excel 365.
In Sheet 2 cell A1 enter =IF(ISBLANK(Sheet1!A1), "",Sheet1!A1)
Select that cell, and on the Home menu click 'Conditional formatting'
Click 'Highlight cell rules', 'More Rules', select 'Blanks' in 'Format only cells with"
Click 'Format' and select the Red color.
OK OK
If there is a value in Sheet1!A1 it will appear in Sheet2!A1, otherwise Sheet2!A1 will be Red.
Works for me.
forrest216 is offline   Reply With Quote
Old 01-29-2020, 05:54 AM   #3
Registered Member
 
Join Date: Aug 2007
Posts: 4
OS: Win 7, Win 8.1



On re-reading your post I believe I may have misinterpreted your question.
Do you want to compare what you enter into Sheet2!A1 with Sheet1!A1, and if it is different, format Sheet2!A1 with Red text? If so:
1. Select Sheet2!A1
2. On the Home menu click 'Conditional Formatting'
3. New Rule
4. Select 'Format Only cells that Contain'
5. First drop-down - leave as 'Cell Value'
6. Second Drop-Down: Select 'Not equal to'
7. In the next field type = Sheet1!$A$1
8. Click 'Format' and select the desired colors for Font, Fill, etc
9. OK OK
Cheers
forrest216 is offline   Reply With Quote
Sponsored Links
Advertisement
 
Old 01-29-2020, 06:30 AM   #4
Registered Member
 
Join Date: Nov 2011
Location: Florida usa
Posts: 14
OS: windows 10 pro



I think I asked it badly. Sheet one is the customer information. the purpose of sheet 2 is to enter orders. Mae beauty salon gets contact infor in sheet 1. to put in a placed order in sheet 2, I need to be sure the salon has it contact info in sheet 1. if not, the sheet 2 cell should turn red to alert the input person that she needs to enter the db info in sheet 1. I will try your second suggestion. Appreciate the input.
cliffmid is offline   Reply With Quote
Old 01-29-2020, 06:57 AM   #5
Windows Tech Team Moderator
Hardware Tech Team Moderator
 
Join Date: Aug 2008
Location: INDIA
Posts: 2,892
OS: Windows 10 | CentOS | Manjaro



Also, if you could share the sheet with dummy data, it can help us narrow down.. We've misinterpreted user requirements in Excel multiple times due to the issue not being articulated clearly..
__________________


tristar is offline   Reply With Quote
Old 01-29-2020, 08:33 AM   #6
Registered Member
 
Join Date: Nov 2011
Location: Florida usa
Posts: 14
OS: windows 10 pro



sure. here's the dropbox link. all names, addresses, etc. are fiction, so don't worry about privacy.


https://www.dropbox.com/scl/fi/l77zz...cyuxerxaxp70ke


I just want to be sure that orders entered are done so with companies established in the DB. The invoice picks off address and so forth from DB. Then, it gets order info, amounts, paid, etc. from the order input sheet.


Have no particular reason to build this. I started it to learn more about dynamic arrays. I am a hobbyist.


Thanks again for your insights.
cliffmid is offline   Reply With Quote
Old 01-30-2020, 04:55 AM   #7
Windows Tech Team Moderator
Hardware Tech Team Moderator
 
Join Date: Aug 2008
Location: INDIA
Posts: 2,892
OS: Windows 10 | CentOS | Manjaro



Here you go:

Highlight Cells B4:B32 and enter the Conditional Formatting formula as:

Code:
=COUNTIF(ContactInfo!$B$4:$B$28, $B4)>0
and Choose the color fill.
__________________


tristar is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is on
Smilies are on
[IMG] code is on
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Post a Question


» Site Navigation
 > FAQ
  > 10.0.0.2
Powered by vBadvanced CMPS v3.2.3


All times are GMT -7. The time now is 05:41 AM.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
vBulletin Security provided by vBSecurity v2.2.2 (Pro) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.1.0 (Pro) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
Copyright 2001 - 2018, Tech Support Forum

Windows 10 - Windows 7 - Windows XP - Windows Vista - Trojan Removal - Spyware Removal - Virus Removal - Networking - Security - Top Web Hosts