highlight minus values in specific column

[MKLAQ]

Hi

I got this code for Rick Rothstein and I modified to fit with my requiremnt but I failed . so what I want any minus values in column E should be red font values and if the minus values return to positive then should return the color to black .

Sub minusvalues()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  Range("E2:E" & LastRow) = Evaluate("IF(E2:E" & LastRow & "<0,RGB(255, 0, 0),E2:E" & LastRow & ")")
End Sub

thanks

​

 

[etaf]

did you want VBA, it can be done just with conditional formatting

 

[MKLAQ]

just question . if CF deal with changes, then should return the color to black again . I mean if I have red minus value and if change to positive , then should be black . if so i don't mind with CF

 

[etaf]

if black is the default colour the allyou are doing is changing to red for <0

but i maybe miss reading

Book2
	A
2	2
3	1
4	-1
5	-2
6	1
7	2
8	1
9	-1
10	-2
11	1
12	2
13	3
14	-1
15	-2
16	1
17	2
18	3
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A18	Expression	=A2<0	text	NO

 

[MKLAQ]

unfortunately it doesn't work to see if it's what I want. It just highlight the first cell is positive despite of I select the whole range when implementation CF.

 

[Peter_SSs]

unfortunately it doesn't work to see if it's what I want. It just highlight the first cell is positive despite of I select the whole range when implementation CF.

If you selected the whole column (and it is column E like your code) then the conditional formatting expression would need to be =E1<0

However, you don't even need conditional formatting which is volatile as you can use a Custom Format

Try this after removing any conditional formatting in the column

Select the column and apply this format (you can see the results in column E at the left of the image)

 

[MKLAQ]

@Peter_SSs
wow ! this is very impressive.
many thanks for your simple solution

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.