Conditional Formatting

M

moorly

New Member
Joined
Dec 25, 2021
Messages
11
Office Version
  1. 2007
Platform
  1. Windows
I have text in cells ofcolum c and either in respective cells contains numbners in column d, e, f, g. I want only colour cells where text and numbers are when i select text from f1 dropdowon list
 

Attachments

  • ex c f.JPG
    ex c f.JPG
    125.4 KB · Views: 9

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
do you want to highlight the row if C matches regardless of the other columns
if so

=$C3=$F$1

Book1
ABCDEF
1****
2
3fred
4****
5harry385
6jill
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:F17Expression=$C3=$F$1textNO



for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
C2:G1000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$C3=$F$1

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

OR
only if a column d e f g contains an entry
and so if all the row is blank it will not highlight
then
=AND($C3=$F$1,OR($D3<>"", $E3<>"", $F3<>"", $G3<>""))


Book1
ABCDEFG
1****
2
3fred
4****1
5harry385
6jill
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:F17Expression=AND($C3=$F$1,OR($D3<>"", $E3<>"", $F3<>"", $G3<>""))textYES
 
Upvote 0
WITH YOUR FORMULAABOVE IT IS NOT WORKING, IT GIVES AGAIN BLANK CELLS COLOURING
 
Upvote 0
Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

--
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: conditional formating
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
i answered at excelforum as well as here - just for completeness, dont usually reply to both , as it can get confusing if other people answer on the forums
BUT as its just me , i have answered BOTH

you have 2007 as the version in this forum and 2010 on the other forum, BUT you also spoecify in a post that its for 2010 version
please update profile , as different versions have different functions available

EDIT
XL2BB does not like the formatting you choose and so its BLACK - i changed to a different FILL


as you said it was a 2010 version

i used 2 conditional formatting rules

selected C4 to c10000
and used

$C4=$F$1
2nd RULE

Selected D4 to G10000
and used

=AND($C4=$F$1,D4<>"")

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
C4:C10000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
$C4=$F$1

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Then added a new rule

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
D4:G10000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND($C4=$F$1,D4<>"")

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

exc con fort (2).xlsx
ABCDEFGH
1HDFC card Payment₹ 13,558.00
2
3DATEBANK NAMEPAYEEFEDERAL DEPOSITFEDERAL DEBITS B I DEPOSITS B I DEBITBALANCE
401-Apr-2024STATE BANK OF INDIAOpening Balance₹ 48,654.50
501-Apr-2024FEDERAL BANKOpening Balance₹ 91,741.51
602-Apr-2024FEDERAL BANKHDFC card Payment4341₹ 136,055.01
702-Apr-2024STATE BANK OF INDIAGas Refund19.57₹ 136,074.58
805-Apr-2024STATE BANK OF INDIASalary35000₹ 171,074.58
907-Apr-2024STATE BANK OF INDIAICICI Card payment10260₹ 160,814.58
1013-Apr-2024STATE BANK OF INDIACash Withdrawal3000₹ 157,814.58
1116-Apr-2024STATE BANK OF INDIAB A Transfer for Exp30000₹ 187,814.58
1217-Apr-2024STATE BANK OF INDIACash Withdrawal - Exp15000₹ 172,814.58
1317-Apr-2024FEDERAL BANKPayment for Rice 760₹ 172,054.58
1422-Apr-2024STATE BANK OF INDIACash Withdrawal5000₹ 167,054.58
1522-Apr-2024FEDERAL BANKCash Withdrawal3000₹ 164,054.58
1623-Apr-2024STATE BANK OF INDIASTD CHART Card Payment7306₹ 156,748.58
1724-Apr-2024STATE BANK OF INDIAPayment for Medicine 698.7₹ 156,049.88
1827-Apr-2024STATE BANK OF INDIACash Withdrawal3000₹ 153,049.88
1929-Apr-2024STATE BANK OF INDIATransfer to F B L25000₹ 128,049.88
2029-Apr-2024FEDERAL BANKTransfer from S B I25000₹ 153,049.88
2130-Apr-2024FEDERAL BANKPayment for Food 510₹ 152,539.88
2230-Apr-2024FEDERAL BANKPayment for Cake630₹ 151,909.88
2301-May-2024STATE BANK OF INDIACash Withdrawal2000₹ 149,909.88
2401-May-2024STATE BANK OF INDIASBI Card Payment2376₹ 147,533.88
2502-May-2024STATE BANK OF INDIAHDFC Card Payment5041₹ 142,492.88
2602-May-2024FEDERAL BANKPayment for A H3400₹ 139,092.88
2706-May-2024STATE BANK OF INDIAPayment for Medicine 735.78₹ 138,357.10
2808-May-2024FEDERAL BANKICICI Card payment13862₹ 124,495.10
2914-May-2024STATE BANK OF INDIASalary35000₹ 159,495.10
3017-May-2024FEDERAL BANKPayment for Software2500₹ 156,995.10
3122-May-2024STATE BANK OF INDIASTD CHART Card Payment11211₹ 145,784.10
3224-May-2024FEDERAL BANKPayment for Sweets360₹ 145,424.10
3325-May-2024STATE BANK OF INDIACash Withdrawal2000₹ 143,424.10
3425-May-2024FEDERAL BANKTemple Expenses1120₹ 142,304.10
3529-May-2024STATE BANK OF INDIACash Withdrawal2000₹ 140,304.10
3631-May-2024STATE BANK OF INDIASBI Card Payment225₹ 140,079.10
3701-Jun-2024STATE BANK OF INDIAHDFC Card Payment4176₹ 135,903.10
3803-Jun-2024FEDERAL BANKCash Withdrawal2000₹ 133,903.10
3905-Jun-2024STATE BANK OF INDIASalary35000₹ 168,903.10
4005-Jun-2024STATE BANK OF INDIATransfer to F B L35000₹ 133,903.10
4105-Jun-2024FEDERAL BANKTransfer from S B I35000₹ 168,903.10
4206-Jun-2024STATE BANK OF INDIAICICI Card payment4360₹ 164,543.10
4310-Jun-2024FEDERAL BANKPayment for Medicine 926₹ 163,617.10
4412-Jun-2024STATE BANK OF INDIATransfer from BA for Exp38000₹ 201,617.10
4512-Jun-2024STATE BANK OF INDIACash Withdrawal - Exp20000₹ 181,617.10
4613-Jun-2024STATE BANK OF INDIAGas Refund19.57₹ 181,636.67
4713-Jun-2024STATE BANK OF INDIACash Withdrawal - Exp18000₹ 163,636.67
4815-Jun-2024STATE BANK OF INDIACash Withdrawal4000₹ 159,636.67
4915-Jun-2024FEDERAL BANKCash Withdrawal5000₹ 154,636.67
5017-Jun-2024FEDERAL BANKCash Withdrawal4000₹ 150,636.67
5120-Jun-2024FEDERAL BANKPayment to Oil360₹ 150,276.67
5221-Jun-2024STATE BANK OF INDIATransfer from BA for Exp15000₹ 165,276.67
5323-Jun-2024STATE BANK OF INDIACash Withdrawal - Exp5000₹ 160,276.67
5425-Jun-2024STATE BANK OF INDIAIntrest Credit385₹ 160,661.67
Sheet1
Cell Formulas
RangeFormula
G1G1=SUMIFS(D:D,C:C, $F$1) + SUMIFS(E:E,C:C, $F$1) + SUMIFS(F:F,C:C, $F$1) + SUMIFS(G:G,C:C, $F$1)
A37,A53,A51,A42A37=IF(B37<>"",IF(A37<>"",A37,TODAY()),"")
H6H6=140396.01+D6-E6+F6-G6
H7:H54H7=H6+D7-E7+F7-G7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4:G1000Expression=AND($C4=$F$1,D4<>"")textYES
C4:C1000Expression=$C4=$F$1textYES
Cells with Data Validation
CellAllowCriteria
B4:B54ListFEDERAL BANK, STATE BANK OF INDIA
F1List=Sheet2!$A$1:$A$22
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Help with Conditional Formatting
Replies
2
Views
180
Peter_SSs
Peter_SSs
B
  • Solved
COUNTIF Conditional Formatting Reversal and leaving rows blank
Replies
7
Views
242
Joe4
Joe4
F
  • Solved
Conditional Formatting Formula
Replies
5
Views
308
dreid1011
dreid1011
drom
  • Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
180
drom
drom
J
  • Question
Sumif vs Sumproduct
2
Replies
11
Views
549
etaf
etaf

Forum statistics

Threads
1,221,494
Messages
6,160,141
Members
451,624
Latest member
TheWes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top