• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

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,
0
Zack
Asked:
Zack
  • 8
  • 6
  • 3
  • +2
1 Solution
 
SiddharthRoutCommented:
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
 
ZackGeneral 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
 
SiddharthRoutCommented:
Is the data in 2 different cells?

Sid
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ZackGeneral IT Goto GuyAuthor Commented:
No all in the same cell.
0
 
SiddharthRoutCommented:
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
 
ZackGeneral 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
 
SiddharthRoutCommented:
Ah Finally I understand :)

Give me few moments.

Sid
0
 
SiddharthRoutCommented:
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

Open in new window

Color-Yellow.xls
0
 
ZackGeneral 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
 
SiddharthRoutCommented:
Not That I am aware of (I could be wrong)

Sid
0
 
Saqib Husain, SyedEngineerCommented:
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
 
ZackGeneral 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
 
aikimarkCommented:
@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

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
0
 
rspahitzCommented:
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
 
aikimarkCommented:
@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
 
ZackGeneral 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.

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.

0
 
Saqib Husain, SyedEngineerCommented:
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
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Saqjb,

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

Thank you.
0
 
Saqib Husain, SyedEngineerCommented:
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
 
ZackGeneral IT Goto GuyAuthor Commented:
Thanks man for the help my apologies for late response something came up.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now