+ Reply to Thread
Results 1 to 3 of 3

Conditional formatting using formula with VBA? - With Example and Code

  1. #1
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Conditional formatting using formula with VBA? - With Example and Code

    Hi all,

    I have a sheet generated by a software every month, so the layout is the same but more or less rows. For this I am writing a code to format it for us as we need it, to make reports from it, so we don't have to format it manually, and I am trying to add some conditional formatting to it but its just not exactly right.. can I get some help please?

    So I have a column with "Something"s in it and I have a column with "Country"-ies in it, and it should never be possible to have "Something1" or "Something2" written beside anything else, only "Country1" or "Country2", but sometimes there are typos and they write the wrong "Something" to some countries and I'm trying set up a conditional formatting rule to highlight it for us if somewhere in the sheet there is "Something1" or "Something2" written beside a country that is not "Country1" or "Country2". I hope this makes sense. See the example attached for visual aid.

    So here is my code to add this conditional formating:
    Please Login or Register  to view this content.
    Now my idea was to write it only to the first row of my table, and then copy the formatting which does the job perfectly when I do it manually, but the problem is, that this VBA code retunrs the actual string written into the cell ...Range(myronr, mycolnr), ...Range(myronr, myotrcolnr) so when I run this and go to manage rules under conditional formatting, I get this formula in the formula bar: =AND(OR(Something45="Something1",Something45="Something2"),AND(Country3<>"Country1",Country3<>"Country2"))

    and thats not right.. I would need to get the cells address - C2 instead of Something45 and - D2 instead of Country3 but I don't know how to do that...

    Is there a way to do this? Could someone help me please?

    Please see the example sheet attached to help understand the problem.

    Thank you all!

    This question is also posted on Mr.Excel Message Board. Link:https://www.mrexcel.com/forum/excel-...ml#post5250188
    Attached Files Attached Files
    Last edited by LIL2606; 03-27-2019 at 12:21 PM. Reason: adding link

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Conditional formatting using formula with VBA? - With Example and Code

    This code copies the conditional formatting down, but I think the code you have to generate the conditional formatting is in error. It should be referencing the table headers and not the header row values. As it is you have conditions like Something45 = "Something1" instead of Something="Something1."

    Please Login or Register  to view this content.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: Conditional formatting using formula with VBA? - With Example and Code

    Hi, yeah the question was how to fix the code for the conditional formatting not how to copy it down, sorry if it wasn't understandable. Thank you for your reply anyway. I found a solution that works for me perfectly, here is the code:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-19-2018, 11:19 AM
  2. Replies: 6
    Last Post: 06-07-2016, 01:50 AM
  3. VBA code for conditional formatting help
    By antoanand1981 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2014, 08:00 AM
  4. VBA code to allow more than 3 conditional formatting
    By DannyJ in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-24-2012, 10:45 AM
  5. Conditional formatting by formula or VB code
    By Abhijit2011 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-18-2011, 07:47 AM
  6. Conditional Formatting Code - Deletes Wrong Conditional Format
    By RSpecianJr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2010, 10:53 AM
  7. Conditional Formatting & VBA Code
    By Sharp in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-05-2007, 06:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1