Conditional Formatting to Highlight if Comma is Found

0267Kim

New Member
Joined
Aug 20, 2019
Messages
13
I have a Conditional Formatting question. Suppose I have a column of dollar amounts(Column G:) with a General number format. Is there a way I can use Conditional Formatting to highlight any amounts which include a comma? I've tried data validation as well. Just can't seem to get Conditional Formatting to work unless I convert the amounts to text.

1620246725328.png
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,012
Office Version
  1. 2016
Platform
  1. Windows
Hi 0267Kim,

None of you example cells contain a comma so are you just trying to highlight cell with thousands or more values?

If so then try

0267Kim.xlsx
FG
2T DateAmount
34/4/2020333
44/4/2020444
54/4/2020555
64/4/20206.66
74/4/2020777
84/4/20201000
94/4/202022222
104/4/2020321
114/4/2020999.99
124/4/20201200
134/4/2020554433
144/4/202022
154/4/2020-1299
16
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G3:G99999Expression=OR($G3>999.99,$G3<-999.99)textNO
 

0267Kim

New Member
Joined
Aug 20, 2019
Messages
13
Thanks for the response. No, I'm not trying to highlight cells with one thousand or more. Instead, what I am hoping to do is use conditional formatting to highlight all cells in column which include a comma. I'm attaching a mini sheet. Hopefully, this is a little more clear. There are multiple cells with amounts over one thousand. I only want to highlight those which include a comma.

Book10.xlsx
A
1AMOUNT
2132431.15
3132,034.15
4466085.59
567303.91
657,948.73
757251.05
837972.12
927050.42
10259949.35
115711.51
12115242.99
1313738.73
141,391.27
15369837.18
16318495.83
17178397.8
Sheet1
 

0267Kim

New Member
Joined
Aug 20, 2019
Messages
13

ADVERTISEMENT

Yeah! That worked, thank you so much!

Book10.xlsx
A
1AMOUNT
2132,431.15
3132034.15
4466,085.59
567303.91
657948.73
757251.05
837972.12
927050.42
10259949.35
115711.51
12115242.99
1313,738.73
141391.27
15369837.18
16318495.83
17178397.8
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A17Expression=","=LEFT(CELL("format",A2),1)textNO
 

0267Kim

New Member
Joined
Aug 20, 2019
Messages
13
Hey, thanks again for the assistance. I had a followup question as well: How can I amend the function above to highlight any comma in any cell in the column? As an example, I've erroneously added a comma to the wrong place in Cell A6.

Book10.xlsx
A
1AMOUNT
2132,431.15
3132034.15
4466,085.59
567303.91
65794,8.73
757251.05
837972.12
927050.42
10259949.35
115711.51
12115242.99
1313,738.73
141391.27
15369837.18
16318495.83
17178397.8
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A17Expression=","=LEFT(CELL("format",A2),1)textNO
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,118
Office Version
  1. 365
  2. 2010
This might do it:

Code:
=OR(ISTEXT(A2),","=LEFT(CELL("format",A2),1))
 

0267Kim

New Member
Joined
Aug 20, 2019
Messages
13
And Just Like that, it works! Thank you again for the help! Yall rock!

Book11.xlsx
A
1AMOUNT
2132,431.15
3132034.15
4466,085.59
567303.91
65794,8.73
757251.05
837972.12
927,050.42
10259949.35
11,5711.51
12115242.99
1313,738.73
141391.27
15369837.18
16318495.83
17178397.8
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A17Expression=OR(ISTEXT(A2),","=LEFT(CELL("format",A2),1))textNO
 

Forum statistics

Threads
1,144,342
Messages
5,723,821
Members
422,518
Latest member
quack_quack

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
Top