I am unsure about what you mean by the other part

>>> says "2.0 Gb Free"

Sid

Untitled.jpg

Solved

Posted on 2011-04-19

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,

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,

20 Comments

I am unsure about what you mean by the other part

>>> says "2.0 Gb Free"

Sid

Untitled.jpg

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

The cell needs to turn yellow if cells contains

27.4 Gb Free

or

2.0 Gb Free

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

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

Sid

```
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
Thats it in macro form I was wondering whether there is way to do this as a conditional formatting statement.

Thank you.

=VALUE(SUBSTITUTE(RIGHT(A1

change the A1 to your cell reference

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

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

I'm worried about the robustness of your posted code.

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

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.)

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.

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

Answering aikimark questions:

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.

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

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

=OR(NOT(ISERROR(FIND("Mb",

Saqib

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

Thank you.

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(A

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

Write further if you need more assistance.

Saqib

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

How to delete a specific cell values in specific rows | 10 | 33 | |

Excel sheet - Calculating 4 processors ! | 11 | 28 | |

Protecting an object | 3 | 23 | |

Dynamic Bar chart in Excel | 4 | 0 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**15** Experts available now in Live!