Here's an easy one for you guys, I just can't get it to work though.

I want to format a cell to change colors dependent on if it falls within a certain range from todays date.

If it's 60 days or less from todays date I want it to turn the cell red

If it's between 61 and 89 days I want the cell to turn orange

If it's between 90 and 105 days I want the cell to turn yellow

The current formulas I'm using are

RED Condition 1: =($F$1-TODAY())

This post is about a success rather than a problem. I have limited experience with Excel and VBA so I haven't been able to give much back to this board for all the help I have received here so I'm posting this in the hope that someone can benefit. There's also a good chance that the code can be improved so feel free to comment on that too.

I have a large worksheet with user-entered data. The cells in each column have data validation turned on (List, Source is a named range, and in-cell drop down is on). This works fine.

The allowed data in the named range changes over time. So some of the data on the worksheet is always out of date but still allowed for now. When it eventually gets updated it must be from the allowed list but if it doesn't get updated right away that's ok.

I wanted a way to show the users which data was out of date so I wrote a function that works from the Conditional Formatting dialog. You pass in the current cell and the named range and the function returns true if the data in the cell is not in the named range and false if it is. Here's the code:
Code:

Function IsNotValidData(thisCellValue As String, controlList As String)

    Dim lookupValue As String
    
    On Error GoTo lookupError
    lookupValue = Application.WorksheetFunction.VLookup(thisCellValue, Range(controlList), 1, False)
    On Error GoTo 0
    IsNotValidData = Not ((UCase(lookupValue) = UCase(thisCellValue)))
    
    Exit Function
    
lookupError:
    lookupValue = ""
    Resume Next
    
End Function

The code works but if there's a better way feel free to post your suggestions.

In the Conditional Formatting dialog you enter: Formula Is =IsNotValidData(H6,"whateverNamedRange") and then set whatever formatting you desire. From there it's easy to apply the formatting to the cells in each column changing the named range in each column as required.

Hi,

Hope somebody can help, I have had a good search, but cant seem to find the correct answer.

I have a table within a sheet (F4:AH63), and I also have a list of initials (A68:A98), I wish to place a list validation into the table so that any of the initials can be selected, but my problem is, that I want the cell colour to change to match the colour of the cell that is in the list of initials.

Is there any way of doing this?

P.s. There are 8 different colours.

Regards

I'm trying to accomplish the same as in this thread HTML Code:

http://www.ozgrid.com/forum/showthread.php?t=145802

AAE

Lock/Unlock Cells Based On Validation Lists
VBA is subject to users choosing to enable macros - which they can elect not to do and thus defeat the code.

First - there is no validation list in cell F11 (or other cells) and the actual cell containing the value is F10 - you have the cells merged (bad idea - causes problems).
However, you can accomplish this without the use of VBA by using Conditional Formatting and Data Validation in conjunction with a helper cell.

Using column-Z as the help column, you could enter this formula: =F10="On Leave"
Which will return TRUE or FALSE; TRUE = F10 contains "On Leave"; FALSE = F10 does not contain it

Set cells J10:P11 to use Conditional Formatting, formula option, using this formula; =$Z10=TRUE
Select a gray fill color
Cells J10:P11 will be colored gray if Z10 = TRUE due to F10 containing "On Leave"

Next . . .
Apply Data Validation to cells J10:P11, using the Custom option and use this formula: =$Z10=FALSE
Set the Error Alert style to Stop and create a custom message if desired.
When Z10 = False it means cells F10 contains "Active" so users can make entries. If Z10 = False then cell F10 contains "On Leave" and Data Validation will prevent users from making entries





I applied this to my spreadsheet, but somehow when the cells are supposed to be cleared of the conditional formatting, only the top cell clears and the rest stays black (color I used).

I did all of it just the same.

except

=A1>1
=A2>1


then for range =$A$3:$E$24
=G1=FALSE then it colors everything black. then I clicked the box Stop if True. when the cell a1 has data, it only clears a3 and the rest stays black

How do I determine if conditional formatting is applied to a spreadsheet cell
in Excel. I tried the Font Object and the Interior object, but they both
apply to normal conditions.

Hi,

I'm trying to put together a spreadsheet that will format the colour of a row based on a value in a data validation list.

The problem is that I am limited by excel 2003. It only allows 3 conditional formats. I need to have a selection of 5 - 8 colours for the different options in the data validation list in column F. If possible I also want to have a conditional format in place for column I. If "h" is selected then it overrides the conditional formatting directed in column F and turns the row a specific colour. Am I asking Excel 2003 to do too much?

I suspect that a VBA code might sort this out easily, but I know little about VBA. I already have a VBA date stamp code in this spreadsheet, so the VBA code will need to be added to and run alongside the existing code. I need something that is relatively people proof and will allow for the insertion of new rows of data within the spreadsheet and at the bottom of the spreadsheet.

Any help is much appreciated. I have attached my spreadsheet so far in excel 2003 format if it is any use.

Many Thanks In Advance,
Joel

Members,

Please help, and provide your input.

Excel 2003 Scenario:
Column H contains text data as follows down the rows: BIKE/CAR/TRUCK. Column N contains text data that would be entered free text by user down the rows; however, if the data entered in column N is not contained in column H it would warn the data is not contained in column H. Example: BOAT is not found in column H. Note; it could contain all text in column H for that row or just one word in column H for that row.
The goal is to recognize the text entered in column N is not contained in column H, warn and change the color of the text in column N to red.

I attempted to use Data/Validation, and Conditional Formatting; none seem to have a contain function.

Is there a formula or VBA I may use or any thoughts?

Thank you

Example:

Cells A1:A4 have conditional formatting set up that states if they are equal
to cell A10 they will be highlighted yellow.

Is there a way to quickly see what cells drive conditional formatting? In
other words, we can use Edit -> Go To Special to see which cells have
conditional formatting applied; however, this doesn't show us that A10 is
involved. Since there isn't a formula directly involved, we can't trace
precedents/dependents. Can we only know that A10 is involved in the
formatting of A1:A4 by selecting those cells and going into the conditional
formatting dialog box? Is there another, faster way?

I've run into a problem with VBA in Excel 2007 while working with conditional formatting. I need to apply a conditional formatting to a data range. However, the subroutine may be run again on the same set of data and I don't want to reapply the conditional formatting to the same data range if it already has the formatting applied. One approach would be simply to delete all conditional formatting on the entire data range, but there could be additional conditional formats which should remain in place. So I'm trying to find a way to cycle through all conditional formatting conditions to see if any of them match the one I'm wanting to apply. If any match what I'm wanting to apply, I'll just skip adding it again.

But I can't seem to find a way in vba to get the formula back out of excel.

Using:
Code:

Workbooks("testbook.xlsm").Sheets("sheet1").ListObjects("DataTable") _
  .DataBodyRange.FormatConditions(index).Interior.ColorIndex

I can have the fill color returned for a given format condition. But is there any way to return the formula in the format condition? I've tried:

Code:

Workbooks("testbook.xlsm").Sheets("sheet1").ListObjects("DataTable") _
  .DataBodyRange.FormatConditions(index).Formula1

but this seems to be only able to set a value, not return one.

Any ideas?

Thanks,

Will

I hypothetically have a dropdown in A1 that gives options of "Percent" or "Currency".

Is there a way to, in conditional formatting or by using a formula, determine the format of a cells style in A2?