# Excel Conditioning Formatting Query

Heyas,

How would I create a conditional formatting statement that tells me when "27.4 Gb Free"  says "2.0 Gb Free" or below (Highlighting the cell in yellow)

Data in the actual cell
C: 40.9 Gb Used / 27.4 Gb Free

Thank you,
###### Who is Participating?

EngineerCommented:
Again a mistake in the first formula. You can change the >= to <=

As regards the working of the formula, you can put this formulas in a cell or break it up into smaller components and study them individually

=NOT(ISERROR(FIND("Mb",A1)))
=NOT(ISERROR(FIND("Kb",A1)))
=VALUE(LEFT(RIGHT(A1,LEN(A1)-FIND("/",A1)),FIND("Free",RIGHT(A1,LEN(A1)-FIND("/",A1)))-4))<=2)

You can even break the value... formula further for ease of understanding.

Write further if you need more assistance.

Saqib
0

Commented:
To highlight the cell which contains "27.4 Gb Free", please see screenshot.

I am unsure about what you mean by the other part

>>> says "2.0 Gb Free"

Sid
Untitled.jpg
0

General IT Goto GuyAuthor Commented:
Hi SiddharthRout,

Data in cell

C: 40.9 Gb Used / 27.4 Gb Free
C: 40.9 Gb Used / 2.0 Gb Free (Hightlight cell in yellow on this)

Thank you
0

Commented:
Is the data in 2 different cells?

Sid
0

General IT Goto GuyAuthor Commented:
No all in the same cell.
0

Commented:
Please correct me if I am wrong.

The cell needs to turn yellow if cells contains

27.4 Gb Free

or

2.0 Gb Free

Sid
0

General IT Goto GuyAuthor Commented:
Hi Sid,

Cell needs to turn yellow if this section  "/ 2.0 Gb Free"  is 2.0 or less.

Examples
C: 40.9 Gb Used / 2.0 Gb Free (Highlight in yellow)
C: 40.9 Gb Used / 1.0 Gb Free (Highlight in yellow)
C: 40.9 Gb Used / 4.0 Gb Free  (No Highlight)

Thank you
0

Commented:
Ah Finally I understand :)

Give me few moments.

Sid
0

Commented:
Is this what you want?

Please run the macro 'Sample' in the attached Excel File

Sid

Code Used

``````Sub Sample()
Dim LastRow As Long, i As Long
Dim MyArray() As String, TmpArray() As String

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow
MyArray = Split(Sheets("Sheet1").Range("A" & i).Value, "/")
TmpArray = Split(Trim(MyArray(1)), " ")
If Val(Trim(TmpArray(0))) <= 2 Then
Sheets("Sheet1").Range("A" & i).Interior.ColorIndex = 6
End If
Next i
End Sub
``````
Color-Yellow.xls
0

General IT Goto GuyAuthor Commented:
Hi Sid,

Thats it in macro form I was wondering whether there is way to do this as a conditional formatting statement.

Thank you.
0

Commented:
Not That I am aware of (I could be wrong)

Sid
0

EngineerCommented:
Try this formula in conditional formatting

=VALUE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("/",A1)),"Gb Free",""))

change the A1 to your cell reference

Saqib
0

General IT Goto GuyAuthor Commented:
Hi Saqib,

That highlights the cell but how would I highlight the cell for this value:

C: 40.9 Gb Used / 2.0 Gb Free And all other below the 2.0 Gb mark for example:
C: 40.9 Gb Used / 1.8 Gb Free

Thank you
0

Commented:
@hellworld12345

I do not think you can accomplish what you want with simple conditional formatting.  Your candidate cells require parsing in order to get the value.  If the candidate cell were a concatenation of two other cell data, you might be able to accomplish it by basing the conditional format criteria to the source cell for the "free" component of the candidate cell contents.

Do the units need to be checked?  For instance, what should happen if the cell contains "C: 40.9 Gb Used / 800 Mb Free"

======
@Sid

If the range contains a formula with a division, what happens?
If the range contains a cell with no "/", what happens?
Since the default value of the VAL() function is zero and zero is less than the 2.0 threshhold, I'm not sure it's exclusionary enough
0

Commented:
I agree with aikimark.
Although this can be done in theory, the formula to determine the value would be overly complex and the size of the formula might exceed Excel's max size limit; also, it would be so complex that if it didn't work exactly right it would be very difficult to identify where the error occurred; similarly, if you decided to change the range, it could be almost as hard to go back and change later.

A better choice in this case it to either write some VBA to handle the task or to use VBA to create a function to determine the value, then use the function inside the conditional formatting (which I've never tired but I think it should work.)
0

Commented:
@rspahitz

I like the idea of a hybrid approach with a custom VBA function doing the parsing and evaluation, feeding the results to a custom format.
0

General IT Goto GuyAuthor Commented:
Heyas,

Thank you for you response. If you are able I would like try a VBA function for use inside conditioning formatting rspahitz mentioned.

Do the units need to be checked?  For instance, what should happen if the cell contains "C: 40.9 Gb Used / 800 Mb Free"  - No The format C: 40.9 Gb Used / 2.0 Gb Free will not change including the units they will remain GB i.e. 0.8 GB Free.

If the range contains a formula with a division, what happens? - This will not happen as the format will not change.
If the range contains a cell with no "/", what happens? This will not happen as the format will not change.

Thank you.

0

EngineerCommented:
hellworld12345,

I am sorry I missed the notification on your comment in response to my last comment.

That formula had a deficiency and can be corrected by

=VALUE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("/",A1)),"Gb Free",""))>=2

Keeping in view subsequent comments from other experts I propose the following modification.

=OR(NOT(ISERROR(FIND("Mb",A1))),NOT(ISERROR(FIND("Kb",A1))),VALUE(LEFT(RIGHT(A1,LEN(A1)-FIND("/",A1)),FIND("Free",RIGHT(A1,LEN(A1)-FIND("/",A1)))-4))<=2)

Saqib
0

General IT Goto GuyAuthor Commented:
Hi Saqjb,

The second formula works great. Question how does the Value A1 work in this formula.

Thank you.
0

General IT Goto GuyAuthor Commented:
Thanks man for the help my apologies for late response something came up.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.