Array set for Conditional Formatting

Hi,

Anyone please help me.

I want to change font color to red if cells value not blanks and less than 3.00.

However it seems the array set does not work =AND($J$2:$J$193<2.995,$J$2:$J$193<>"")

try:

1. select J2
2. in CF type: =J2<3
3. in CF select font color (in this caae: red)
4. close CF
5. use Format Painter (J2 is selected) and paint as far as you need (prob. to J193)
6. You don't need second condition because if cell is blank you don't see any font and color

or simpler:
1. select J2:J193
2. CF type =J2<3
3. select font color
4. OK and close CF
5. the same as #6 above

Hi sandy,

sorry. Actually to change Cells color & Font. not only font.


I have place this formula to "Format values where this formula true"
=AND($J$2:$J$193<2.995,$J$2:$J$193<>"")

but it does not work.

what's the difference font or background or both?

select your range and use in CF: =AND($J2<3,$J2<>"") then choose colors.

Edit:
Your formula [ =AND($J$2:$J$193<2.995,$J$2:$J$193<>"") ] will work IF all values in range will be less than 3 and all cells in range will not be blank. IF any value will be =3 or >3 or any cell will be blank - color will not be changed.

Hi sandy,

CF: =AND($J2<3,$J2<>"") working fine

but edited formula which involves Array set does not work [ =AND($J$2:$J$193<2.995,$J$2:$J$193<>"") ] or =AND($J$2:$J$193<2.995,$J$2:$J$193<>"")

we also cannot apply curly braces {} CTRL+SHIFT+ENTER in conditional Formatting rules, right ?


I think the problem is how to apply array set in CF rules. I'm really frustrated.
I am familar using macro

For Each rngCell In wsht.Range("J2:J" & wsht.Cells(Rows.Count, "J").End(xlUp).Row)
If rngCell.Value < 3 Then rngCell.Font.Color = vbRed
Next rngCell

but for this issue it is not efficient method because Microsoft has already built up function for users

square brackets was for show formula not for use with brackets
formula cannot be entered as array into CF.
your formula is:

Formula:

Please Login or Register  to view this content.

with no brackets. but like I said above: it works in CF only (show colors) if every cell in range is not empty AND every value in range is less than 3. (or 2.995 , whatever)

In Excel Formulas & Functions I am not VBA hero. Maybe in Excel Programming / VBA / Macros if I must. But I prefer formulas and function

Thanks for feedback. I have added reputation to you even tough not resolving my problem.


hopefully some else may help me how to apply multiple range (Array Set) in CF

CF: =AND($J2<3,$J2<>"") working fine

CF: =AND($J$2:$J$193<3,$J$2:$J$193<>"") does not work

Formulas entered into the CF refedit (that's what that little box is called) will automatically be evaluated as an array formula and there's no need to use the key combination of CTRL,SHIFT,ENTER to produce the squiggly brackets { } (which will not work in the refedit).

Hi Tony/sandy,

I have tried the formula but it does not work

Attached here is my workbook
https://maybank.sharefile.com/share?...04da?_k=mu1yvd

seek assistance please

in range type: 1,2,1,-2,0,1,2,2,1,0 all < 3 you will see red. if any of cells will be empty range will lost color. if any of values willl be < 3 - range will lost color. enough ONE cell with number >=3 or ONE empty cell to clear range from color.

but basically you did not tell what exactly you want to achieve

Hi sandy,

I have open your workbook. There is no red color for those mentioned range. i have put these values for "J1:J10" 1,2,1,-2,0,1,2,2,1,0

Anything wrong with my Office? I will try at home tonight.

Hi sandy,

Finally, I understand what you mean, changing cell in data validation to 0 will cause all cells in range "J1:J10" turn to red. If any cells in these range change to >= 3, the red color will gone for entire range "J1:J10". It is NOT what I want. I want CF to change for only cell value <3 not entire range.

e.g. if cell values in J5 <3, only J5 turn to red.

J1 4
J2 2 ---- cell J2 turn to red
J3 3
J4 6
J5 1 ---- cell J5 turn to red
J6 7
''
''
J10 9

it is misunderstood between myself and you.
I think it is NOT the accurate formula =AND($J$2:$J$193<3,$J$2:$J$193<>"") .

I cannot create CF for each cell because range of data is determine by range end Cells(Rows.Count, "J").End(xlUp).Row

Hi John,

Thanks a lot problem solved, Set Applies to

I feel really confused, what went wrong with this? why we can't apply array formula in CF, =AND($J$2:$J$193<3,$J$2:$J$193<>"")

I think my poor understanding on CF

Originally Posted by Faridwahidi

=AND($J$2:$J$193<3,$J$2:$J$193<>"")

With that formula EVERY cell within the range would have to be a number <3.

It's evaluating ALL cells as a group when you need to evaluate EACH individual cell.

Thanks tony.

You're welcome!