Conditional formatting - isblank option

[UHsoccer]

When a cell is empty, the "isblank(A1)" formula works fine.
But
If I enter a number of spaces, the formaula 1 (isblank) fails.

Here are my curent condtions

1) Formula - =IsBlank(J3) then background Yellow

2) Cell Value is - not equal to - =("OK") then Red

3) Cell Value is - equal to - ="OK" then Green

 

[UHsoccer]

I copied your solution (from Report) and it stays red for NO-blanks or any number of blanks for that matter. it seemed to ignore the condition.

Amazing that such a simple concept as "If a cell APPEARS to be empty, then..." can get so difficult.

Also the TRIM function did not do

Nor the IfCellIsBlank Function

I am close to resurecting my old resume and go back to ......

Just the same, it is a great forum.

 

[north19701]

that's odd, the formula I posted worked on my spreadsheet.

 

[Yesuslave]

Hey,
OK, found the problem with the IfCellIsBlank function, just a minor spelling error, but the code works fine on my machine.

If Len(ActiveCell.Text) = 0 or IfCellIsBlank(ActiveCell.text) = True Then
'code to format cell to Yellow
Elseif ActiveCell.Text = "OK" Then
'code to format cell to Green
Else
'code to format cell to Red.
End if


Function IfCellIsBlank(strText As String) As Boolean



For i = 1 To Len(strText)
If Mid(strText, i, 1) <> " " Then
IfCellIsBlanks = False
Exit Function
End If
Next

IfCellIsBlank = True

End Function

 

[Aladin Akyurek]

this is is for condition yellow.
=LEN(J3)-LEN(SUBSTITUTE(J3," ",""))=LEN(J3)

=LEN(J3)=LEN(SUBSTITUTE(J3," ",""))

would be shorter. In fact, much shorter:

=TRIM(J3)=""

 

[north19701]

yes, yes it is...

 

[Yesuslave]

Sorry, one more change, the IfCellIsBlanks below should be IfCellIsBlank (no "s") at the end.

For i = 1 To Len(strText)
If Mid(strText, i, 1) <> " " Then
IfCellIsBlanks = False
Exit Function
End If
Next

IfCellIsBlank = True

End Function

 

[Aladin Akyurek]

When a cell is empty, the "isblank(A1)" formula works fine.
But
If I enter a number of spaces, the formaula 1 (isblank) fails.

Here are my curent condtions

1) Formula - =IsBlank(J3) then background Yellow

2) Cell Value is - not equal to - =("OK") then Red

3) Cell Value is - equal to - ="OK" then Green

Try:

1)

=TRIM(J3)="" for yellow.

2)

=(TRIM(J3)<>"")*(J3<>"OK") for red.

3)

=J3="OK" for green.