I need to put conditional formatting into an Excel sheet. When one cell has data entered, the entire row must have the font color changed. Any suggestions?

Re: vb.net build excel sheet with conditional formatting 80 80

May want to edit your tags as the question/topic seems to be about Excel and not VBA or VB.NET.

So I went to Google this and put in this question:

How do I apply conditional formatting to an entire row based on one cell?

The answer came back as this: https://trumpexcel.com/highlight-rows-based-on-cell-value/
At step 8, you would choose FONT rather than FILL.

PS. Since there are more than a few steps to this and your posting tabs look incorrect I'm replying with a link instead. I may be guessing you wanted this in VB code or a macro instead of conditional formatting.

Re: vb.net build excel sheet with conditional formatting 80 80

I may not have explained correctly. I'm building the sheet with vb.net. I need to put code into it using .net, so that when the user is entering data into the sheet the row font changes.

Re: vb.net build excel sheet with conditional formatting 80 80

Sorry vbforum, if I'm entering data into a sheet that would, to me say I'm using Excel so the conditional formatting there is what we use.

If you are using VB.NET CODE then you didn't supply much indication or the code you are using to call up Excel etc.

How about creating the spreadsheet in Excel with the conditional formatting then that sheet is what you call up from VB.NET?

Re: vb.net build excel sheet with conditional formatting 80 80

found a way to make it work. Still confused though..
This works, but you have to specify each cell in the row.
With xlsh2.Range("A" & intRow.ToString).FormatConditions.Add(XlFormatConditionType.xlExpression, Formula1:="=NOT(ISBLANK(O" & intRow.ToString & "))")
With .Font
.ColorIndex = 0
End With
End With
For a reason unknownst to me, this does not change the font on anything but the first cell:
With xlsh2.Range("A" & intRow.ToString & ":O" & intRow.ToString).FormatConditions.Add(XlFormatConditionType.xlExpression, Formula1:="=NOT(ISBLANK(O" & intRow.ToString & "))")
With .Font
.ColorIndex = 0
End With
End With
Also tried unsuccessfully to use:
With xlsh2.Range("A" & intRow.ToString).EntireRow.FormatConditions.Add(XlFormatConditionType.xlExpression, Formula1:="=NOT(ISBLANK(O" & intRow.ToString & "))")
With .Font
.ColorIndex = 0
End With
End With

Re: vb.net build excel sheet with conditional formatting 80 80

In response to generating the spreadsheet in Excel first:
The user will be generating the spreadsheet when they run the program. They need it loaded and formatted when it generates.

Re: vb.net build excel sheet with conditional formatting 80 80

As to the generation of the sheet, for a very long time folk would have a blank sheet with the needed format then copy that to use in the new sheet.

In VB.NET that would be one line of code. A copy command.

Also back in Excel there are xltx (Excel template) and xltm (Excel macro-enabled template; same as xltx but may contain macros and scripts) files that we call on when we need repeatitive formats.

Re: vb.net build excel sheet with conditional formatting 80 80

This works, but you have to specify each cell in the row.

Noted elsewhere:

Conditional Formatting is the same as adding one or more formulas to each cell in which you use it

Example from https://www.c-sharpcorner.com/UploadFile/f0e144/excel-apply-conditional-formatting-in-VB-Net/
I'd say that's a pretty specific prior as they note we have to apply the conditional format to each cell plus they show changing the font color.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.